CODEWITHSUNDEEP
sql-server
Đức Trần
Đức Trần

Reputation: 89

Select @variable From Where

I have table "q_rank" with 2 column Q_ID and KTTH

q_id    KTTH
1        1
2        1

I create this procedure

 CREATE PROCEDURE [dbo].[test] 
    @Param1    varchar(10)
AS
BEGIN
DECLARE @query nvarchar(MAX);

    SET @query= (SELECT  @Param1 FROM [Exam].[dbo].[q_rank] a where q_id=2)
    SELECT @query

END

But when I

EXEC test 'KTTH'

The result is KTTH, but I want it is 1. please help me

Upvotes: 0

Views: 81

Answers (2)

Patrick Hofman
Patrick Hofman

Reputation: 157098

Try use sp_executesql:

DECLARE @outval tinyint;

SET @query= 'SELECT @outval = ' + @Param1 + ' FROM [Exam].[dbo].[q_rank] a where q_id=2';
sp_executesql @query, N'@outval tinyint', @outval = @outval output;

return @outval;

Upvotes: 1

JamieA
JamieA

Reputation: 2013

you need Dynamic SQL

CREATE PROCEDURE [dbo].[test] 
    @Param1    varchar(10)
AS
BEGIN
DECLARE @query nvarchar(MAX);

    SET @query= 'SELECT ' + @Param1  + ' FROM [Exam].[dbo].[q_rank] a where q_id=2'
    exec  (@query)

END

Upvotes: 0

Related Questions