将参数传递给存储过程 [英] passing parameter to store procedure

问题描述

以下是我的存储过程，会将二进制数据转换为bigint

  ALTER   proc 

[dbo]. XXX( @ Value   nvarchar ( 4000 ) ， @ returnValue   BIGINT   OUTPUT )
为
开始
选择 @ returnValue = 转换( bigint ， cast( @ Value   as   binary )) 来自 [dbo].[## Mytemp];

结束 

我将col名称作为参数传递但未提供输出，当我用表的实际colname替换该参数时(例如col4示例正在工作)
我需要将列名作为参数传递?

SP

解决方案

中的任何更正可以将列名作为参数传递，在这种情况下，必须动态执行查询:

例如:

exec(''select convert(bigint,cast('' + @Value + '' as binary)) from [dbo].[##Mytemp]'');

这将为您提供记录集，而不是输出参数.

您也可以在使用sp_executesql时查看以下链接:

  DECLARE   @ i   INT ， @ sql   NVARCHAR ( 512 )
 SET   @ sql  = N ' 从' +  @ dbname  +  ' .INFORMATION_SCHEMA.TABLES'
 EXEC  sp_executesql  @ query  =  @ sql ，  @ params  = N '  @ i INT'OUT'' span>， @ i  =  @ i  输出 

http://sqlserver2000. database.aspfaq.com/how-do-i-get-the-result-of-dynamic-sql-into-a-variable.html [ 创建 过程 dbo.countTables @ dbname SYSNAME AS 开始 DECLARE @ i INT ， @ sql NVARCHAR ( 512 ) SET @ sql = N ' 从'中选择@i = COUNT(*) + @ dbname + ' .INFORMATION_SCHEMA.TABLES'跨度> EXEC sp_executesql @查询 = @ sql ， @ params = N ' @ i INT'OUT''跨度>， @ i = @ i 输出 打印 @ i END GO

此处的详细说明
http://www.informit.com/articles/article.aspx?p=25288& seqNum = 6 [ ^ ]

谢谢
--RA

hi ,
below is my storeprocedure will convert the binary data into bigint

ALTER proc

[dbo]. XXX (@Value nvarchar(4000),@returnValue BIGINT OUTPUT)
as
Begin
select @returnValue= convert(bigint,cast(@Value as binary))  from [dbo].[##Mytemp];

End

am passing col name as parameter but it is not giving output,when i replace that parameter with actualy colname of table i.e (example col4) is working
i nedd to pass column name as parameter is it possible?

any correction in the SP

解决方案

You can pass column name as parameter, in that case you have to execute the query dynamically:

example:

exec(''select convert(bigint,cast('' + @Value + '' as binary)) from [dbo].[##Mytemp]'');

This will give you the recordset and not the output parameter.

You can also look at the following link on using sp_executesql:

DECLARE @i INT, @sql NVARCHAR(512)
SET @sql = N'SELECT @i = COUNT(*) FROM ' + @dbname + '.INFORMATION_SCHEMA.TABLES'
EXEC sp_executesql @query = @sql, @params = N'@i INT OUTPUT', @i = @i OUTPUT

http://sqlserver2000.databases.aspfaq.com/how-do-i-get-the-result-of-dynamic-sql-into-a-variable.html[^]

CREATE PROCEDURE dbo.countTables
    @dbname SYSNAME
AS
BEGIN
    DECLARE @i INT, @sql NVARCHAR(512)

    SET @sql = N'SELECT @i = COUNT(*) FROM '
        + @dbname + '.INFORMATION_SCHEMA.TABLES'

    EXEC sp_executesql
        @query = @sql,
        @params = N'@i INT OUTPUT',
        @i = @i OUTPUT

    PRINT @i
END
GO

Detailed explanation here
http://www.informit.com/articles/article.aspx?p=25288&seqNum=6[^]

Thanks
--RA

这篇关于将参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！