CODEWITHSUNDEEP
sqlsql-serverstored-proceduresparameters
Zajn
Zajn

Reputation: 4088

Error when declaring parameter in stored procedure

I am trying to create a stored procedure that utilizes a variable number of parameters. As I am pretty new to writing stored procedures and TSQL in general, I decided to try and write it with only one parameter. However, I keep getting an error stating "Must declare scalar variable @FirstName" when I try to execute it. Right now, I am trying to store the SQL statement in another variable, @sql. My procedure looks like this:

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
(@FirstName varchar(50))

AS

BEGIN

    DECLARE @sql AS NVARCHAR(4000)
    SET @sql = 'SELECT e.* from Employee e
               WHERE e.FirstName = @FirstName'
    EXEC (@sql)
END

I've looked elsewhere and tried EXEC sp_execute @sql which didn't work. Strangely, what does work is when I don't declare the @sql variable and instead just write my query normally. Since that is the case, I'm assuming there is some error in my usage of the SET and EXEC functions. I'm also not 100% sure that I'm using BEGIN and END properly. The way I understood it is that BEGIN and END separate SQL statements into logical blocks, and thus are more commonly used when IF comes into play. Could anyone tell me what exactly is going on with my parameter here? It's really confusing me as to why SQL Server thinks it's not declared.

Upvotes: 0

Views: 6405

Answers (6)

Oleg Dok
Oleg Dok

Reputation: 21776

Use this body, without "dynamic" query

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
(@FirstName varchar(50))

AS

BEGIN

    SELECT e.* from Employee e
               WHERE e.FirstName = @FirstName
END

OR

using dynamic query do not include variables itselves into the script - do concatenate them with the script and do not forget to properly quote them.

Upvotes: 0

Callie J
Callie J

Reputation: 31326

As this is a "search" type query your doing with a variable number of params, you need to build the string up bit by bit -- you're on the right lines that it needs to be dynamic, but you also need to avoid SQL injection attacks (google "Little Bobby Tables"). Thus you need to use a parameterised dynamic SQL statement:

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
    @FirstName VARCHAR(50)
AS
BEGIN
    DECLARE @sql AS NVARCHAR(4000)

    SET @sql = 'SELECT e.* FROM Employee e WHERE 1 = 1'
    IF @FirstName IS NOT NULL
    BEGIN
        SET @sql = @sql + ' AND FirstName = @pFirstName'
    END
    -- More IF statements, building up the query

    EXEC sp_ExecuteSQL @sql, N'@pFirstName VARCHAR(50)', @FirstName

The second and third params map the @FirstName parameter to the query's "internal" parameters (which I normally prefix with a 'p' or 'param' just to differentiate them from the stored procedure's own parameters).

You extend the sp_Exceute as appropriate each time you add a new parameter to search by, so you might end up doing:

    EXEC sp_ExecuteSQL @sql,' N'
              @pFirstName   VARCHAR(50),
              @pSurName     VARCHAR(50),
              @pDateOfBirth DATETIME', @FirstName, @Surname, @DateOfBirth

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

When you execute dynamic sql, you are switching contexts and variables don't move between contexts. Once you declare the SQL statement as a string, everythign must be provided to that string in order for it to recognize it.

Obviously, you don't need dynamic SQL in this case, but once method of doing it is like so:

    ALTER PROCEDURE [dbo].[GetEmployeeByParameters] 
  (@FirstName varchar(50))      
AS
      BEGIN
          DECLARE @sql AS NVARCHAR(4000)
       SET @sql = 'SELECT e.* from Employee e
                  WHERE e.FirstName = @FirstName'
       EXEC sp_executeSQL @sql, N'@Firstname varchar(50)', @FirstName
   END

sp_executeSQL allows you to declare internal parameters (the second clause), and supply them with values (the last clause).

Upvotes: 1

Kev Ritchie
Kev Ritchie

Reputation: 1647

You need to change your query to the following as the @Firstname variable is not in scope:

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
(@FirstName varchar(50))

AS

BEGIN

    DECLARE @sql AS NVARCHAR(4000)
    SET @sql = 'SELECT e.* from Employee e
               WHERE e.FirstName = ''' + @FirstName + ''''
    EXEC (@sql)
END

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

The variable parameter needs to be outside the quotes.

SET @sql = N'SELECT e.* from Employee e
             WHERE e.FirstName = ''' + @FirstName + ''''

Or, better yet, run it without any dynamic SQL.

SELECT e.* 
    from Employee e
    WHERE e.FirstName = @FirstName

Upvotes: 3

msarchet
msarchet

Reputation: 15242

Because 

'Select  ... @FirstName'

is not the same as

Select ... @FirstName

One is a string and the other is a SQL Query

What you should do instead is

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
(@FirstName varchar(50))

AS

BEGIN

    DECLARE @sql AS NVARCHAR(4000)
    SET @sql = 'SELECT e.* from Employee e
               WHERE e.FirstName = ''' + @FirstName + ''''
    EXEC (@sql)
END

Upvotes: 1

Related Questions