Set variables in stored procedure using EXECUTE sp_executesql

Question

I'm trying to build an SQL string to execute via sp_executesql but I can't seem to assign value to a declared variable within the passed in sql string.

So below is an example of my procedure

ALTER PROCEDURE [dbo].[selectRecords]
@psID INT --parameter passed in
AS   
DECLARE @existingRecordCount INT=0

DECLARE @sql NVARCHAR(1000)
DECLARE @paramDefinitions NVARCHAR(1000)

SET @paramDefinitions=
    '@psID INT,    
    @existingRecordCount INT OUTPUT'

SET @sql='  
SELECT 
@existingRecordCount=COUNT(Name)
FROM dbo.Asset_Log
WHERE ID=@psID
GROUP BY Name'

EXECUTE sp_executesql @sql, @paramDefinitions, @psID, @existingRecordCount

So, I'd expect my record count to be in the @existingRecordCount variable but I receive the following error:

Incorrect syntax near 'OUPUT'.
Must declare the scalar variable "@existingRecordCount".

What am I doing wrong?

Thanks in advance. I'm using SQL Server 2008

`OUTPUT` not `OUPUT`. Also call like `EXECUTE sp_executesql @sql, @paramDefinitions, @psID, @existingRecordCount OUTPUT` and declare `@psID` outside and assign it the value to be passed in. — Martin Smith, Jul 16 '12 at 15:26
Martin, thanks. Sorry about the Typo. Aaron, I've just modified the example for this post — Sun, Jul 16 '12 at 15:36

score 5 · Answer 1 · answered Jul 16 '12 at 15:39

While this doesn't need to be dynamic SQL:

ALTER PROCEDURE [dbo].[selectRecords]
  @psID INT
AS
BEGIN
  SET NOCOUNT ON; 

  DECLARE @rc INT;

  SELECT @rc = COUNT(Name) 
    FROM dbo.Asset_Log
    WHERE ID = @psID;
END
GO

I'll assume there is other logic here that you haven't shared that makes it a necessity. If that is the case:

ALTER PROCEDURE [dbo].[selectRecords]
  @psID INT
AS
BEGIN
  SET NOCOUNT ON; 

  DECLARE 
    @rc     INT = 0,
    @sql    NVARCHAR(1000),
    @params NVARCHAR(1000);

  SET @params = N'@psID INT, @rc INT OUTPUT';

  SET @sql = N'SELECT @rc = COUNT(Name)
    FROM dbo.Asset_Log
    WHERE ID = @psID;';

  EXEC sp_executesql @sql, @params, @psID, @rc OUTPUT;
END
GO

I don't see any reason for GROUP BY Name, either. Have you observed how that changes the query from a single result to a row per name?

Hi Aaron. Thanks for the answer. The group by is actually needed as in the full SQL I have other columns returned — Sun, Jul 17 '12 at 07:16

score 1 · Answer 2 · 2012-07-16T15:45:55.833

1

Remove Dynamic Query syntax and Group By Clause

ALTER PROCEDURE [dbo].[selectRecords]
@psID INT --parameter passed in
AS       
SET NOCOUNT ON;
SELECT COUNT(Name) as existingRecordCount FROM dbo.Asset_Log WHERE ID=@psID

edited Jul 16 '12 at 15:45

answered Jul 16 '12 at 15:40

score 0 · Answer 3 · answered Feb 17 '16 at 12:05

Try this, Its working

CREATE PROCEDURE [dbo].[deleteItem]
    @ItemId int = 0,
    @status bit OUT

AS
Begin
   DECLARE @cnt int;
   SET @status =0;  --Set value to variable 
   return @status;
End

Execute store procedure

We are passing ItemId as 6 at the time of execute stored procedure

Out put will receive on "@statuss" variable

DECLARE @statuss bit;
EXECUTE  [dbo].[deleteItem] 6, @statuss output;
PRINT @statuss;

Set variables in stored procedure using EXECUTE sp_executesql

3 Answers3