CODEWITHSUNDEEP
sqlsql-server
Joseph
Joseph

Reputation: 11

SQL Server : how to declare a variable in stored procedure

I need help for developing a stored procedure which has a select query joining tables from two different server.

Example:

CREATE PROCEDURE [dbo].[test proc]
    DECLARE @customerid INT

    SELECT OL.CUSTOMER_ID
    FROM CUSTOMERS C
    JOIN SERVER2.ORDER.ORDERLIST OL ON C.ID = OL.CUSTOMER_ID
    WHERE OL.CUSTOMER_ID = @customerid

They are linked servers.

This stored procedure will be in the Customer database on Server1.

Can I make server2 a variable? As I need the user to specify the server name and customerid when running the stored procedure. I need the stored procedure to be able to execute in production and test environment. Or how should I do it?

Upvotes: 1

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You need to use dynamic SQL:

create PROCEDURE [dbo].[test proc] (
    @customerid int,
    @server2 sysname  -- or you can use nvarchar(255)
)
begin
    declare @sql nvarchar(max);

    set @sql = '
SELECT OL.CUSTOMER_ID
FROM CUSTOMERS C JOIN
     @SERVER2.ORDER.ORDERLIST OL
     ON C.ID = OL.CUSTOMER_ID
WHERE OL.CUSTOMER_ID = @customerid';

    set @sql = replace(@sql '@SERVER2', @server2);

    exec sp_executesql @sql,
                       N'@customerid int',
                       @customerid=@customerid;
end;

sp_executesql allows you to replace constant values in the dynamic SQL. However, you are not permitted to change identifiers, such as server names, which is why this uses replace().

Upvotes: 2

Related Questions