Introduction to SQL string statements

Recently, when I was writing a stored procedure, I found a problem about introducing parameters to the string SQL statements in the stored procedure.

See the following example:

declare @count intselect @count=count(*) from student where Name in ('Chamy','Jundy')
print @count

If we want to introduce parameters after In the above sentence:

It must be written as follows:

declare @count intdeclare @Names nvarchar(200)set @Names='''Chamy'',''Jundy'''declare @sql nvarchar(500)set @sql='select @count=count(*) from student where Name in ('+@Names+')'exec(@sql)print @count

Here, the @ count parameter is printed, but an error is reported during SQL Execution. The @ count parameter is not defined. After thinking about it, we found that, because we only exec the SQL statements in @ SQL, the @ count parameter is indeed not defined. As a public parameter, what should I do if I want to get the @ count parameter after @ SQL statement execution?

After searching for various materials and asking senior SQL developers, you can solve the problem as follows:

declare @count intdeclare @Names nvarchar(200)set @Names='''Chamy'',''Jundy'''declare @sql nvarchar(500)set @sql='select @count=count(*) from student where Name in ('+@Names+')'EXEC sp_executesql @sql,N'@count int OUTPUT',@count OUTPUTprint @count

We noticed that the @ SQL parameter can be executed in the sp_executesql stored procedure, and the @ count parameter can be redefined, and the @ count parameter other than @ SQL can be used for output, it can handle the problem of parameter input.

Description of the sp_executesql stored procedure and use link address: http://technet.microsoft.com/zh-cn/library/ms188001.aspx

The above are my problems found in the actual development process, and I hope to help you.

For more WEB development technologies, add Asp. Net high-level group number: 261882616 bloggers and colleagues to discuss topics of interest with you.