CODEWITHSUNDEEP
sqlsql-server
Emeka C
Emeka C

Reputation: 53

SQL Server demands scalar variable declaration

I have the following SQL code in my Management Studio to create a stored procedure. When trying to execute the query, I get an error message:

Must declare the scalar variable "@Id"

But I have already declared @Id - what could be the problem?

My code: 

CREATE PROCEDURE [dbo].[DecryptMyName]  
     (@Id Int,
      @MyName varbinary(MAX))
AS
GO
OPEN SYMMETRIC KEY SymmetricKeyxx
DECRYPTION BY CERTIFICATE Certificatexx;
GO
-- Now list the original ID, the encrypted ID 
SELECT CONVERT(nvarchar, DecryptByKey(MyName)) 
FROM dbo.MyTable WHERE Id=@Id ;

 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKeyxx;
GO

Upvotes: 0

Views: 106

Answers (2)

mansi
mansi

Reputation: 877

Just Remove 'Go' word in your query then it will works

This way:

    CREATE PROCEDURE [dbo].[DecryptMyName]  
         (@Id Int,
          @MyName varbinary(MAX))
    AS

    OPEN SYMMETRIC KEY SymmetricKeyxx
    DECRYPTION BY CERTIFICATE Certificatexx;

    -- Now list the original ID, the encrypted ID 
    SELECT CONVERT(nvarchar, DecryptByKey(MyName)) 
    FROM dbo.MyTable WHERE Id=@Id ;

     -- Close the symmetric key
    CLOSE SYMMETRIC KEY SymmetricKeyxx;

For Best Practices always use BEGIN and END statement for your stored procedure like this:

    CREATE PROCEDURE [dbo].[DecryptMyName]  
         (@Id Int,
          @MyName varbinary(MAX))
    AS
    BEGIN
    OPEN SYMMETRIC KEY SymmetricKeyxx
    DECRYPTION BY CERTIFICATE Certificatexx;

    -- Now list the original ID, the encrypted ID 
    SELECT CONVERT(nvarchar, DecryptByKey(MyName)) 
    FROM dbo.MyTable WHERE Id=@Id ;

     -- Close the symmetric key
    CLOSE SYMMETRIC KEY SymmetricKeyxx;
    END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

GO is not allowed in a stored procedure body. I would recommend that you always enclose the body in BEGIN/END to avoid this problem.

Does this work?

CREATE PROCEDURE [dbo].[DecryptMyName]  (
    @Id Int ,
    @MyName varbinary(MAX)
) AS
BEGIN
    OPEN SYMMETRIC KEY SymmetricKeyxx
    DECRYPTION BY CERTIFICATE Certificatexx;

    -- Now list the original ID, the encrypted ID 
    SELECT CONVERT(nvarchar, DecryptByKey(MyName)) 
    FROM dbo.MyTable WHERE Id=@Id ;

     -- Close the symmetric key
    CLOSE SYMMETRIC KEY SymmetricKeyxx;

END;

If not, you will need to use dynamic SQL.

Upvotes: 1

Related Questions