32607

SP_EXECUTESQL and Output Parameter

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?

Answer1:

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

Answer2:

Like Deepak's answer, but more easy:

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

Recommend

  • wp-admin can't access after moving
  • Configuring an Access report to use a SQL Server stored procedure as its record source
  • An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.
  • Setting Maven for scripting languages?
  • Fresh Rails 4 app is not recognizing `rails` commands, insists that I use `rails new`
  • Why do I have to use LD_PRELOAD for libcl.2 to use XML::LibXML::Common on HP/UX?
  • Maven - which projects or techologies you are using it for?
  • Open a file from the same package
  • Reading POM's with its children
  • How to preserve alias property while signing app?
  • Why won't my rails spec run?
  • Find the associated program to open a file using Java
  • Development workflow for server and client using Docker Compose?
  • Launching a new command window from Golang in Windows
  • How to escape colon (:) character while executing native SQL queries against an Informix database us
  • Writing to Ontotext GraphDB using Jena
  • How to use : function in H2O ddply, R?
  • INSERT EXEC Statement cannot be nested [duplicate]
  • Get used tables from sql query [duplicate]
  • Portable JRE on Linux - possible?
  • How to retrieve information from antrun back to maven?
  • pillow imaging ImportError
  • Security issues with PHP's Readfile method
  • Z3: Convert between FP and BitVector?
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • onBackPressed() not being executed
  • How to install a .deb file on a jailbroken iphone programmatically?
  • Algorithm for a smudge tool?
  • Counter field in MS Access, how to generate?
  • Can Jackson SerializationFeature be overridden per field or class?
  • Deserializing XML into class C#
  • Convert array of 8 bytes to signed long in C++
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • Delete MySQLi record without showing the id in the URL
  • Android Studio and gradle
  • python draw pie shapes with colour filled
  • Programmatically clearing map cache
  • How to Embed XSL into XML