February 10, 2003 at 2:56 am
Hi there,
I need to create a dynamic query inside a stored procedure. The dynamic query should accept 3 parameters. Those parameters contain
1. Fields required in the result set
2. Table to use
3. Condition to be checked for selecting records
I tried in the following way, but couldnt get through. Note that here I tried with condition being dynamic
DECLARE @Condition nvarchar(100)
DECLARE @SQLString NVARCHAR(500)
/* Build the SQL string once.*/
SET @SQLString =
N'SELECT * FROM customer WHERE ' + @Condition
/* Execute the string with the first parameter value. */
set @Condition= N'CustomerID > 1400'
EXECUTE sp_executesql @SQLString, @Condition
on executing this, am getting error msg that reads : Incorrect syntax near 'CustomerID'
Can anybody help me?
Thanx in advance
February 10, 2003 at 3:36 am
Try this
DECLARE @Condition nvarchar(100)
DECLARE @SQLString NVARCHAR(500)
set @Condition = ' <whatever condition> '
SET @SQLString = 'SELECT * FROM customer WHERE ' + @Condition
EXECUTE sp_executesql @SQLString
-- Now execute the second condition
set @Condition = 'CustomerID > 1400'
SET @SQLString = 'SELECT * FROM customer WHERE ' + @Condition
EXECUTE sp_executesql @SQLString
Ashish
February 10, 2003 at 9:06 pm
Thanks Ashish, it works! I think I did the same with some minor errors, right?
Anyway, can u suggest me how to extend this query where I can specify the table name and the fields to be fetched on the fly?
February 11, 2003 at 2:00 am
Hi,
Try something like this. What I have done is that I have taken table name, field name, data type of field name and actual value of field name in variables. According to data type then I am forming the query. Hope this helps you.
DECLARE atTableName NVARCHAR(100)
DECLARE atFieldName NVARCHAR(100)
DECLARE @atFieldType INT
DECLARE atFieldValue NVARCHAR(100)
SET atTableName = '<TABLENAME>'
SET atFieldName = '<FIELDNAME>'
SET atFieldType = 1 -- FOR CHAR, 0 FOR NUMBER ......
SET atFieldValue = '1'
SET @tstrQuery = ' SELECT * FROM ' + @atTableName
' WHERE ' + @atFieldName + ' = '
IF(@atFieldType = 1) -- Char datatype
BEGIN
SET @tstrQuery = @tstrQuery + '''' + @atFieldValue + ''''
END
ELSE-- numeric datatype
BEGIN
SET @tstrQuery = @tstrQuery + @atFieldValue
END
EXECUTE sp_executesql @tstrQuery
Ashish
February 11, 2003 at 2:59 am
Well Ashish,
Nice to receive ur immediate reply. But u might have posted it in hurry cause it contains some typo errors. Here is the corrected one.
DECLARE @TableName nvarchar(100)
DECLARE @FieldName NVARCHAR(100)
DECLARE @FieldType INT
DECLARE @FieldValue NVARCHAR(100)
DECLARE @tstrQuery NVARCHAR(500)
SET @TableName = 'Customer'
SET @FieldName = 'CustomerName'
SET @FieldType = 1 -- FOR CHAR, 0 FOR NUMBER ......
SET @FieldValue = 'brett'
SET @tstrQuery = ' SELECT * FROM ' + @TableName + ' WHERE ' + @FieldName + ' = '
IF(@FieldType = 1) -- Char datatype
BEGIN
SET @tstrQuery = @tstrQuery + '''' + @FieldValue + ''''
END
ELSE -- numeric datatype
BEGIN
SET @tstrQuery = @tstrQuery + @FieldValue
END
EXECUTE sp_executesql @tstrQuery
And in the meanwhile I worked out another one, and here it is:
DECLARE @Condition nvarchar(100)
DECLARE @SQLString NVARCHAR(500)
DECLARE @Tablename nvarchar(100)
DECLARE @Fieldstoget nvarchar(200)
set @Fieldstoget = ' CustomerID, CustomerName '
set @Tablename = ' Customer '
set @Condition = ' customerid > 1400 '
SET @SQLString = 'SELECT ' + @Fieldstoget + ' FROM ' + @Tablename + ' WHERE ' + @Condition
EXECUTE sp_executesql @SQLString
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply