I have a table which stores a SQL predefined function, like CONVERT(VARCHAR(10), '2012-08-21 00:16:41.993', 101) in a row/column. While retrieving the result from table, it should run the function and give the final outcome as "2012-08-21", instead right now it returns the same function statement. I am running select (select RunDate from RunDate) and using SQL server database.
Kindly help!!
You need to use dynamic SQL for this. You can't just nest expressions and have SQL evaluate the output...
DECLARE @x TABLE(sql NVARCHAR(255));
INSERT @x(sql) SELECT N'CONVERT(VARCHAR(10), ''2012-08-21 00:16:41.993'', 101)';
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = N'SELECT ' + sql FROM @x;
EXEC sp_executesql @sql;
It would look like this (adjust accordingly):
DECLARE @predef VARCHAR(1000);
DECLARE @sqlquery VARCHAR(1000);
SELECT @predef = (SELECT top 1 Value FROM Parameters Where Name = 'MYFUNC');
SET @sqlquery = 'select ' + @predef + ' from SomeTable';
EXECUTE ( @sqlquery );
One tip: here be dragons. Beware of SQL Injection.
SELECT @predef = SELECT which doesn't work. Drop the second SELECT. Second line constructs a query that has an opening parenthesis but not a closing one.
DECLARE @x TABLE(sql NVARCHAR(255)); INSERT @x(sql) SELECT N'CONVERT(VARCHAR(10), ''2012-08-21 00:16:41.993'', 101)'; INSERT @x(sql) SELECT N'CONVERT(VARCHAR(10), ''2012-08-18 00:16:41.993'', 101)'; DECLARE @sql NVARCHAR(MAX); SELECT @sql = N'SELECT ' + sql FROM @x; EXEC sp_executesql @sql; it's always give me a single result-set, result is : **2012-08-18** My question is, how can i get both the record in single result set?like :2012-08-21 2012-08-18