Doubt in StoredProcedure [modified]

John L Ponratnam

create proc sp_temp @columnname varchar(25),@value varchar(25) as declare @sql nvarchar(400) set @sql='select *from tblname where '+@columnname+' = '+@value exec(@sql) This is the code. This procedures works for integer value when I insert in @value but for a string value; it gives invalid column name. Can anyone help me out -- modified at 2:50 Monday 29th October, 2007

Regards, LEE

neeraj_indianic

Just check your table detail. It will be because of you have defined the column name as int while creating the table.

N a v a n e e t h

Print your SQL query before executing, you will get the problem.

My Website | Ask smart questions

soni uma

Try This it's help u set @sql='select * from jc_student_master where '+@columnname+' = '''+@value+''' '

John ph

If you are using EXEC command to execute a sql String then the parameter has to converted into Character and made as a part of the query before execution. try this...

set @sql = 'select * from tblname where ' + 
@columnname + ' = ' + CAST(@value AS NVARCHAR(50)) 
EXEC(@sql)

Regards
- J O H N -

John L Ponratnam

Let me have table name tblname which has empid and empname. In the paramater @columnname empid and @value(int) this query gets the perfect output. But If I use @columnname empname and @value as string; then it gives invalid column name

Regards, LEE

John ph

ok I suggest you a better way of doing this. In sql Server we have one method of executing Dynamic Sql using sp_executesql which does a parameter substution based on the parameter definition.Let us do something like this to solve your problem...

create proc sp_temp 
@columnname varchar(25),
@value varchar(25) 
as 
 
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
 
set @SQLQuery = 'select * from tblname where (1=1) AND ' 
 
IF @columnname = 'EmpID'
BEGIN
set @SQLQuery = @SQLQuery + @columnname + ' =  @value' 
SET @ParameterDefinition =  '@value INT'
END
 
IF @columnname = 'EmpName' 
BEGIN
set @SQLQuery = @SQLQuery + @columnname + ' = @value' 
SET @ParameterDefinition =  '@value NVARCHAR(25)'
END
 
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @value
 
GO

Here in the above procedure I build the ParameterDefinition list with the appropriate Datatype based on the column name. And the sp_executesql would automatically do a parameter substitution based on the DataType. Jus check it out whether it works...

Regards
- J O H N -

neeraj_indianic

ok, Now i got your problem and your solution is given here. set @sql='select *from tblname where '+@columnname+' = '+''''+@value+'''' I have tried it and its working.