Help for a Stored Proc.

[mukund]

Help required for a stored proc. Refer the stored proc given below.

The id_val table has columns
name nvarchar(25)
val1 int
val2 int
val3 int
val4 int

These columns can increase till val9, which will be handled programatically through a VB component.

The problem in the stored proc given below is that I can't
uncommnet the val5 to val9 statements in the Case structure.

The reason for this is the stored proc. won't compile, because the columns don't exist as of now, they will be added at any point of time.

I tried using dynamic SQL but to no avail, I can't use a local variable declared in the scope stored proc. don't work in Dynamic SQL.

Any soultions, suggestions are welcome.

Thanks,
Mukund.


CREATE PROCEDURE generateid
@inid nvarchar(25),
@loc_id int
AS
DECLARE @loc_val int, @id_val int, @final_id int, @SQL varchar(100)
select @id_val=
CASE @loc_id
WHEN 1 THEN val1
WHEN 2 THEN val2
WHEN 3 THEN val3
WHEN 4 THEN val4
/*WHEN 5 THEN val5
WHEN 6 THEN val6
WHEN 7 THEN val7
WHEN 8 THEN val8
WHEN 9 THEN val9 */
END
from id_val
where name = @inid

 

[tlbroadbent]

Here is a query using dynamic SQL that should work.

CREATE PROCEDURE generateid
@inid nvarchar(25),
@loc_id int
AS

SET quoted_identifier off
SET nocount ON

DECLARE @id_val int, @SQL varchar(1000)

SET @SQL="SELECT " +
CASE @loc_id
WHEN 1 THEN "val1"
WHEN 2 THEN "val2"
WHEN 3 THEN "val3"
WHEN 4 THEN "val4"
WHEN 5 THEN "val5"
WHEN 6 THEN "val6"
WHEN 7 THEN "val7"
WHEN 8 THEN "val8"
WHEN 9 THEN "val9"
END + " FROM id_val WHERE name = '" + @inid + "'"

EXEC @id_val = sp_executesql @sql Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[mukund]

Hi Terry,
Thanks for the Help, but still I am facing a problem with it. The query executes perfectly, shows a value being returned but does not assign the value to the local variable.

I think there is still some problem with

EXEC @id_val = sp_executesql @SQL

I checked the value of @id_val, after the statement is executed, but the value is 0.

Mukund.

 

[mukund]

Hi Terry,
The problem is the return value by using sp_executesql is 0 for success and 1 for failure therefore the
EXEC @id_val = sp_executesql @SQL
statement is assigning 0 values to @id_val indicating that sp_executesql @SQL statement executed successfuly, it is not returning any value, returned by the select statement.

Is there anyway in which I can get the value returned ?

Mukund.

 

[tlbroadbent]

Mukund,

My mistake. Try this procedure instead.

CREATE PROCEDURE generateid
@inid nvarchar(25),
@loc_id int
AS

SET quoted_identifier off
SET nocount ON

DECLARE @id_val int, @SQL varchar(1000)

SET @SQL="SELECT @id_val" +
CASE @loc_id
WHEN 1 THEN "val1"
WHEN 2 THEN "val2"
WHEN 3 THEN "val3"
WHEN 4 THEN "val4"
WHEN 5 THEN "val5"
WHEN 6 THEN "val6"
WHEN 7 THEN "val7"
WHEN 8 THEN "val8"
WHEN 9 THEN "val9"
END + " FROM id_val WHERE name = '" + @inid + "'"

exec sp_executesql @sql, N'@id_val int output', @id_val=@id_val Output Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[mukund]

Hi Terry,
Thanks aton. This did the trick. You has only missed a '=' sign after @id_val in the statement
SET @SQL="SELECT @id_val" +

But I managed to figure that out.

Thanks again,
Mukund.