passing parameter to store procedure

Question

0.00/5 (No votes)

See more:

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

SQL

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

Posted 1-Feb-12 18:21pm

pradeep manne

Updated 1-Feb-12 19:00pm

_Maxxx_

v2

Add a Solution

3 solutions

Solution 3

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

Thanks
--RA

Posted 1-Feb-12 19:21pm

Rajesh Anuhya

Solution 2

SQL

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

Posted 1-Feb-12 19:02pm

Rakesh Meel

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Om Prakash Pant · Accepted Answer · 2012-02-01T18:47:00

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:

SQL

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[^]

passing parameter to store procedure

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

passing parameter to store procedure

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us