How to Pass column names to query dynamically
-
you may declare and use dynamic sql like this;
CREATE PROCEDURE GetRecords ( @DynamicColumnName varchar(100) ) AS
/* Create a variable @SQLStatement */
DECLARE @dynamicsql varchar(255)/* Enter the dynamic SQL statement into the*/
/* variable @SQLStatement*/
SELECT @dynamicsql = 'SELECT Column1, Column2, Column3 ' +
'FROM TableName ORDER BY ' + @DynamicColumnName/* Execute the SQL statement*/
EXEC(@dynamicsql)Hope this will help.
________________ Tehmina Qureshi ****************
Dear Sir, Thanks for your help.But it's not comming here.Can you tel me how to use that in this query.Here is my query alter procedure LogProc ( @UserId varchar(500), @Password varchar(500) ) as Begin declare @str1 varchar(500) declare @str2 varchar(500) declare @str3 varchar(500) declare @str4 varchar(500) select @str1=AstId from UserTable where UserId=@UserId and Password = @Password; select @str2=RoleID from AstProf where AstId =@str1 ; set @str4=@str2; select @str3=MenuId from MenuTable where @str4='y'; exec(@str4); select @str3; End exec LogProc 'veeresh','veeresh' But its giving error Like this (1 row(s) affected) Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'R1'.
i want to join this group
-
Dear Sir, Thanks for your help.But it's not comming here.Can you tel me how to use that in this query.Here is my query alter procedure LogProc ( @UserId varchar(500), @Password varchar(500) ) as Begin declare @str1 varchar(500) declare @str2 varchar(500) declare @str3 varchar(500) declare @str4 varchar(500) select @str1=AstId from UserTable where UserId=@UserId and Password = @Password; select @str2=RoleID from AstProf where AstId =@str1 ; set @str4=@str2; select @str3=MenuId from MenuTable where @str4='y'; exec(@str4); select @str3; End exec LogProc 'veeresh','veeresh' But its giving error Like this (1 row(s) affected) Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'R1'.
i want to join this group
Veer, you did not fully understood the concept of dynamic sql. so 1st you have to understand what the dynamic sql is; Dynamic SQL is actually the complete sql statement, including all the necessory clauses required for executing the sql statemnt like as "select * from tablename" so if u hav a variable declared for dynamic sql u must assign a complete statement in it. here in ur sp u did wrong. Example:Declaration of dynamic sql
Declare @DynamicSql varchar(2000)
set @DynamicSql = 'SELECT * FROM MyTable'now you may execute the like this
exec(@DynamicSql) /* See ! Here in execute i've given the variable name nott any procedure */
You may do aything which you want to with Dynamic SQL. I did not understand what you want to select in you stored procedure (as i dont hav enough time to understand the code coz im at my job. May be after few time i'll correct your code and post it back to u.) If you have got an idea about Dynamic SQL you can do whatever you want. Still hav problem? You are welcome to query. Thanks
________________ Tehmina Qureshi ****************
-
Dear Sir, Thanks for your help.But it's not comming here.Can you tel me how to use that in this query.Here is my query alter procedure LogProc ( @UserId varchar(500), @Password varchar(500) ) as Begin declare @str1 varchar(500) declare @str2 varchar(500) declare @str3 varchar(500) declare @str4 varchar(500) select @str1=AstId from UserTable where UserId=@UserId and Password = @Password; select @str2=RoleID from AstProf where AstId =@str1 ; set @str4=@str2; select @str3=MenuId from MenuTable where @str4='y'; exec(@str4); select @str3; End exec LogProc 'veeresh','veeresh' But its giving error Like this (1 row(s) affected) Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'R1'.
i want to join this group
Hi, Thank you very much sir.I wil follow the rules. Veeresh.
i want to join this group
-
you may declare and use dynamic sql like this;
CREATE PROCEDURE GetRecords ( @DynamicColumnName varchar(100) ) AS
/* Create a variable @SQLStatement */
DECLARE @dynamicsql varchar(255)/* Enter the dynamic SQL statement into the*/
/* variable @SQLStatement*/
SELECT @dynamicsql = 'SELECT Column1, Column2, Column3 ' +
'FROM TableName ORDER BY ' + @DynamicColumnName/* Execute the SQL statement*/
EXEC(@dynamicsql)Hope this will help.
________________ Tehmina Qureshi ****************
hi IsrarMuhammadQ i have error to execute dynamic sql in function
-
hi IsrarMuhammadQ i have error to execute dynamic sql in function
Send the code and error msg i'll try to figure it out Can u describe me in words what you want to do? just describe it clearly i'll try to make you the actual understanding about what u are asking.
________________ Tehmina Qureshi We can have facts without thinking but we cannot have thinking without facts. ****************
-
Send the code and error msg i'll try to figure it out Can u describe me in words what you want to do? just describe it clearly i'll try to make you the actual understanding about what u are asking.
________________ Tehmina Qureshi We can have facts without thinking but we cannot have thinking without facts. ****************
this example : -------------------------------------- CREATE FUNCTION test_fn ( @x int ) RETURNS @table TABLE ( c1 int ) AS BEGIN EXEC('select * from @table') RETURN END ----------------------------- Msg 443, Level 16, State 2, Procedure _fn__Get_All_References, Line 35 Invalid use of 'EXECUTE' within a function.
-
this example : -------------------------------------- CREATE FUNCTION test_fn ( @x int ) RETURNS @table TABLE ( c1 int ) AS BEGIN EXEC('select * from @table') RETURN END ----------------------------- Msg 443, Level 16, State 2, Procedure _fn__Get_All_References, Line 35 Invalid use of 'EXECUTE' within a function.
Hi, Dont make it complicated. just try this in Northwind database.
Create PROCEDURE test_GetRecords ( @DynamicColumnName varchar(100) ) AS
/* Create a variable @dynamicsql */
DECLARE @dynamicsql varchar(255)/* Enter the dynamic SQL statement into the*/
/* variable @dynamicsql*/
SELECT @dynamicsql = 'SELECT * ' +
'FROM [Categories] ORDER BY ' + @DynamicColumnName + ' desc'/* Execute the SQL statement*/
EXEC(@dynamicsql)exec test_GetRecords '[CategoryName]'
________________ Tehmina Qureshi We can have facts without thinking but we cannot have thinking without facts. ****************
-
Hi, Dont make it complicated. just try this in Northwind database.
Create PROCEDURE test_GetRecords ( @DynamicColumnName varchar(100) ) AS
/* Create a variable @dynamicsql */
DECLARE @dynamicsql varchar(255)/* Enter the dynamic SQL statement into the*/
/* variable @dynamicsql*/
SELECT @dynamicsql = 'SELECT * ' +
'FROM [Categories] ORDER BY ' + @DynamicColumnName + ' desc'/* Execute the SQL statement*/
EXEC(@dynamicsql)exec test_GetRecords '[CategoryName]'
________________ Tehmina Qureshi We can have facts without thinking but we cannot have thinking without facts. ****************
i want use dynamic sql in function not in stored procedure
-
i want use dynamic sql in function not in stored procedure
Oops, sorry - Basically, no, you cannot use dynamic SQL in a function, and you probably shouldn't use it anyway. Writing to a temporary table is prohibited by functions.
________________ Tehmina Qureshi We can have facts without thinking but we cannot have thinking without facts. ****************
-
Oops, sorry - Basically, no, you cannot use dynamic SQL in a function, and you probably shouldn't use it anyway. Writing to a temporary table is prohibited by functions.
________________ Tehmina Qureshi We can have facts without thinking but we cannot have thinking without facts. ****************
ok Thanks where r u from?