CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
flerngobot
flerngobot

Reputation: 666

Creating a stored procedure using variables

Is there any good way to do this, or am I just heading in the wrong direction? I would like to create a stored procedure inside an SQL script. I would like to have variables declared at the beginning of the script so that I can create the SPROCs to use in different contexts/servers.Here is what I would like to do (I know this obviously doesn't work, but I'm looking for any ideas of an alternative)..

DECLARE @golbalValue = 'SomeValue'
GO

CREATE PROCEDURE [dbo].[MyStoredProcedure](
AS
BEGIN

SELECT * FROM Mytable WHERE MyCol = @globalValue
END
GO

Upvotes: 0

Views: 208

Answers (3)

Stephan
Stephan

Reputation: 6018

From what I understand, you need to create stored procedures with set value from your parameters. You don't want input parameters in the stored Procedures though. Second, you want to switch database contexts. So I think you'll need a tempTable for your parameters and some dynamic SQL. Try this out:

IF OBJECT_ID('tempdb..#globalParam') IS NOT NULL 
    DROP TABLE #globalParam;
IF OBJECT_ID('AdventureWorks2012.dbo.myTable') IS NOT NULL 
    DROP TABLE AdventureWorks2012.dbo.myTable
IF OBJECT_ID('Master..myTable') IS NOT NULL 
    DROP TABLE Master..mytable

--Create your data tables
SELECT  'SomeValue' AS col1 INTO AdventureWorks2012.dbo.myTable;
SELECT  1000        AS col1 INTO master.dbo.myTable;


CREATE TABLE #globalParam(
                            ParamName VARCHAR(100), 
                            val SQL_VARIANT --SQL_Variant is designed to hold all data types.
                         );

--Here are your globalParams
DECLARE @globalParam1 VARCHAR(100) = 'SomeValue';
DECLARE @globalParam2 INT = 1000;

--Load your parameters into a table. Might have to cast some of your parameters to SQL_Variant
INSERT INTO #globalParam 
VALUES  ('globalParam1',@globalParam1),
        ('globalParam2',CAST(@globalParam2 AS sql_variant));
GO

--Switch database context
USE AdventureWorks2012
GO
--Variable to hold CREATE PROC
DECLARE @sql VARCHAR(MAX);

--Set @SQL with parameter value from #globalParam
SELECT @sql =
'CREATE PROCEDURE dbo.myStoredProc AS
BEGIN
    SELECT * FROM myTable WHERE col1 = ''' + CAST(val AS VARCHAR(100)) + '''
END'
FROM #globalParam
WHERE ParamName = 'globalParam1'

--Execute to create the stored procedure
EXEC(@sql)
--Execute it to see if it works
EXEC dbo.myStoredProc

--Switch context. Repeat same steps
USE master
GO
DECLARE @sql VARCHAR(MAX);
SELECT @sql =
'CREATE PROCEDURE dbo.myStoredProc AS 
BEGIN
    SELECT * FROM myTable WHERE col1 = ''' + CAST(val AS VARCHAR(100)) + '''
END'
FROM #globalParam 
WHERE ParamName = 'globalParam2'


EXEC(@sql)
EXEC dbo.myStoredProc


--Cleanup
DROP PROCEDURE dbo.myStoredProc;
USE AdventureWorks2012
GO
DROP PROCEDURE dbo.myStoredProc;

Upvotes: 1

T I
T I

Reputation: 9933

What you could do is use a scalar function for the variable

create function f ()
returns varchar(20)
as
begin
    return 'some value'
end
go

then use it in your procedure

create proc p ()
as
begin
    select * 
    from my_table
    where col = f()
end
go

another possibility which is perhaps more appropriate is to use sqlcmd here's an example.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You cannot do what you want. T-SQL doesn't have the concept of global variables. One method is to store values in a "global" table and then reference them as needed. Something like:

create table GlobalParams (
    name varchar(255) not null primary key,
    value varchar(255) not null
);
create procedure . . .
begin
    . . .
    declare @value varchar(255);
    select @value = value from Globalparams where name = 'name';

    select *
    from Mytable
    where MyCol = @value;

    . . .
end;

Note: this is a simplistic example that only allows variables whose type is a string.

You can also wrap the logic in a user-defined function, so the call looks like:

    select *
    from Mytable
    where MyCol = udf_GlobalLookup('name');

It is rather rare to need global parameters that are shared among different stored procedures. Such a global context can be useful, at times, for complex systems. It is unlikely that you need all this machinery for a simple application. An alternative method, such as just passing the parameters in as arguments, is probably sufficient.

Upvotes: 0

Related Questions