SP_EXECUTESQL and Output Parameter

Question

I would like get the ID from the query, but I am getting a NULL, where is my mistake?

DECLARE @TblZimz    NVARCHAR(256)
DECLARE @IdModul    INTEGER
DECLARE @Id         INTEGER

SET @TblZimz        = '_ZIMZ000001'
SET @IdModul        = 1
--SET @Id               = -1

EXECUTE [InsertZimz] @TblZimz, @IdModul, @Id OUTPUT




ALTER PROCEDURE [InsertZimz]
@TblZimz    NVARCHAR(256)
, @IdModul  NVARCHAR(256)
, @Id       INTEGER OUTPUT

            DECLARE @SqlQuery NVARCHAR(MAX)
        SET @SqlQuery = 'SELECT TOP (1) ([ID]) FROM ' + @TblZimz + ' WHERE [ModulId] = ' + @IdModul

        EXEC SP_EXECUTESQL @SqlQuery, N'@Id INTEGER OUTPUT', @Id OUTPUT

why the @Id Paramter is alwasy null? I cant see my mistake?

If you are going to allow the table name as parameter (not a great idea) you need to wrap the parameter inside QUOTENAME at the very least to help prevent sql injection. — Sean Lange, Dec 15 '14 at 14:24
Also, use top without an order by is not a good practice. You don't know which row is going to be returned and it can change between executions. You should always have an order by when using TOP. — Sean Lange, Dec 15 '14 at 14:26

Deep · Accepted Answer · 2014-12-15T15:00:15.683

26

First, select the desired id in an output variable using @Id = ([ID]) then assign this @Id OUTPUT value in the @Id variable using @Id = @Id OUTPUT. Also, you should pass data in where clause using a variable to avoid sql injection problem like [ModulId] = @IdModul (i.e. you should not concatenate it like [ModulId] = ' + @IdModul). try this :

DECLARE @SqlQuery NVARCHAR(MAX)

SET @SqlQuery = 'SELECT TOP (1) @Id = ([ID]) FROM '
                + @TblZimz + ' WHERE [ModulId] = @IdModul'

EXEC SP_EXECUTESQL
  @SqlQuery,
  N'@Id INT OUTPUT, @IdModul INT',
  @IdModul = @IdModul,
  @Id = @Id OUTPUT

Check details of SP_EXECUTESQL here

edited Dec 15 '14 at 15:00

answered Dec 15 '14 at 14:23

Deep

3,162
1
12
21

@Id = ([ID]) perfekt! – MeerArtefakt Dec 15 '14 at 14:27
but, what do you mean with: "You are not selecting the id in a variable also you should pass data in where clause using a variable to avoid sql injection problem" – MeerArtefakt Dec 15 '14 at 14:44
1

@MeerArtefakt elaborated the answer. – Deep Dec 15 '14 at 14:58

score 3 · Answer 2 · edited Mar 06 '23 at 11:29

3

Like Deepak's answer, but easier:

EXEC SP_EXECUTESQL @SqlQuery,
N'@Id INT OUTPUT, @IdModul INT',
@IdModul OUTPUT, @Id

edited Mar 06 '23 at 11:29

Marcello Miorelli

3,368
4
44
67

answered Aug 01 '18 at 09:21

Eric Draven

259
3
7

bwperrin · Answer 3 · 2022-08-10T15:18:40.890

Here's an example of returning a dynamically defined proc's return value (rather than select results):

CREATE PROC p AS return 3
GO
DECLARE @proc varchar(30) = 'p', @retval int
DECLARE @qry nvarchar(max) = 'EXEC @i = ' + @proc --plus params as needed
EXEC sp_executesql @qry, N'@i INT OUTPUT', @retval OUTPUT
select @retval      --returns 3

(Use case: to do a full "data run," I cursor through a list of procs I run, each of which returns a row count for logging purposes. Chased my tail quite a while before cracking this.)

SP_EXECUTESQL and Output Parameter

3 Answers3

Linked

Related