sp_executesql Receive returns multiple argument instances

Source: Internet
Author: User

Recently doing the project needs to be in the exec SQL string when the dynamic incoming parameters and receive the return value, so the study of SQL Server in the use of sp_executesql, and do the following example.
In the use of sp_executesql dynamic incoming and receive return parameters should pay attention to the following things to avoid people detours.
Example @sqlstring, @ParmDefinition be sure to use the nvarchar type, otherwise it will be reported that "the process requires parameters ' xxxx ' for the ' Ntext/nchar/nvarchar ' type." Error
The document said that the two variables assigned to the string before to add N, I tried to try, did not add or error, but you'd better add. After all, are they official documents?

--Passing an int type argument
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR (500)
DECLARE @ParmDefinition NVARCHAR (500)

SET @SQLString = N ' SELECT * from pubs.dbo.employee WHERE job_lvl = @level '
SET @ParmDefinition = N ' @level tinyint '
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable
--set @IntVariable = 32
--execute sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable
-------------------------------------------------------------------------------
--Passing a varchar type parameter
DECLARE @VarVariable varchar (500)
DECLARE @SQLString NVARCHAR (500)
DECLARE @ParmDefinition NVARCHAR (500)

SET @SQLString = N ' SELECT * from pubs.dbo.employee WHERE fname = @VarVariable '
SET @ParmDefinition = N ' @VarVariable varchar (500) '
SET @VarVariable = ' Helen '
EXECUTE sp_executesql @SQLString, @ParmDefinition, @VarVariable
-------------------------------------------------------------------------------
--Pass two parameters, a varchar type parameter to receive the return value, an int type argument
DECLARE @VarVariable VARCHAR (500)
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR (500)
DECLARE @ParmDefinition NVARCHAR (500)

SET @SQLString = N ' SELECT @VarVariable = Count (*) from pubs.dbo.employee WHERE job_lvl = @IntVariable '
SET @IntVariable = 35
SET @ParmDefinition = N ' @VarVariable VARCHAR ($) out, @IntVariable INT '
SET @VarVariable = ' Helen '
EXECUTE sp_executesql @SQLString, @ParmDefinition, @VarVariable out, @IntVariable
Print @VarVariable


sp_executesql Receive returns multiple argument instances

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.