June 10, 2015 at 2:04 pm
Hi,
I need some help in building the dynamic sql . When I create below script and executed the procedure passing the table name and date values its giving me error saying "Incorrect syntax near '>'". Any help is very highly appreciated.
Create PROCEDURE sampleprocedure
@tablename AS VARCHAR(4000),
@date as date
AS
BEGIN
declare @table varchar(1000)
declare @invoiceDate date
set @table = @tablename
set @invoiceDate = @date
declare @query nvarchar(1000);
set @query='SELECT * FROM '+ @table +'where invoiceDate >= @date';
exec(@query)
END)
Thanks...
June 10, 2015 at 2:09 pm
CREATE PROCEDURE sampleprocedure
@tablename AS varchar(4000),
@date AS date
AS
SET NOCOUNT ON;
DECLARE @query nvarchar(4000);
SET @query = 'SELECT * FROM ' + QUOTENAME(@tablename) +
' WHERE invoiceDate >= ''' + CONVERT(varchar(8), @date, 112) + ''''
EXEC(@query)
GO --end of proc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 10, 2015 at 2:27 pm
A different way. Modify the SET and EXEC lines of Scott's code above as follows:
SET @query = 'SELECT * FROM ' + QUOTENAME(@tablename) +
' WHERE invoiceDate >= @dateIn';
EXEC sys.executesql @query, N'@dateIn date', @dateIn = @date;
June 10, 2015 at 2:31 pm
Thank you for the quick reply.
I implemented the logic, but some how when I passed the table name it says invalid object name. The way I created is I created the stored procedure in one database for example I created the stored procedure in database1 and I am passing the tablename from different database as database2.schema.tablename. But its throwing me an error saying that its an invalid object , how ever when I queried directly from database1 like select * from database2.schema.tablename its not saying that object is invalid. Any help is highly appreciated.
Thanks
Heera
June 10, 2015 at 2:37 pm
srgaddam2 (6/10/2015)
Thank you for the quick reply.I implemented the logic, but some how when I passed the table name it says invalid object name. The way I created is I created the stored procedure in one database for example I created the stored procedure in database1 and I am passing the tablename from different database as database2.schema.tablename. But its throwing me an error saying that its an invalid object , how ever when I queried directly from database1 like select * from database2.schema.tablename its not saying that object is invalid. Any help is highly appreciated.
Thanks
Heera
That is because of the QUOTENAME function. It is putting '[' and ']' around the entire value of @tablename. You can do the following as long as there are no '.' in the database, schema, or table name:
REPLACE(QUOTENAME(@tablename),'.','].[')
June 10, 2015 at 2:37 pm
Scott's fix will work quite nicely.
I was already typing up a solution like Scott's, and when I saw his had posted, I didn't want the typing to go to waste, so I'll just leave in the explanation 🙂
In case you're curious, here are the issues with the initial query:
1) The table name, @table, was not quoted. That wasn't the reason for the error you were receiving, but using QUOTENAME is a nice future-proofing touch, in case you have table names with hyphens or other such characters in them.
2) There was no space before 'WHERE' in the constructed string, so the table name and 'WHERE' would have been concatenated (if the table name were 'Sometable', then the query would have had 'Sometablewhere invoicedate'. That was causing the exact error you posted.
3) You weren't actually appending the value of the date parameter to the query string. Instead, '@date' was being added as a literal string, so when executed that query would expect a variable @date to have been declared in its scope (within the dynamic T SQL). Had you fixed problem 2 from above, the next error you would have encountered would have been one telling you that you must declare the scalar variable @date, for this reason.
4) Had you attempted to fix problem 3 by concatenating @date with the rest of the string, like this: set @query='SELECT * FROM '+ @table +'where invoiceDate >=' + @date
then you would have found that SQL Server would complain of VARCHAR and DATE types being incompatible for addition. That's why Scott converted it to VARCHAR (and specified the ISO format, which is what 112 is).
5) In the constructed query, the date-converted-to-string would need to be enclosed in single quotes, and to put literal single quotes as characters in to a string, they must be escaped (otherwise SQL Server will think that you are closing the string, not adding a single quote to the string itself). That's why you see multiple single quotes in a row in Scott's solution. Single quotes can be escaped by following them immediately with another single quote. That last '''' in Scott's query begins a string (first single quote), adds a single quote as a character in the string (the next two single quotes, where the second one escapes the first), and then the fourth single quote closes the string.
Cheers!
EDIT: I took too long to post this, so there's been quite a bit of back and forth. Pardon the lack of timeliness 🙂
June 10, 2015 at 2:40 pm
Sorry, I was being lazy and didn't include checks for db name and schema:
ALTER PROCEDURE sampleprocedure
@tablename AS varchar(4000),
@date AS date
AS
SET NOCOUNT ON;
DECLARE @query nvarchar(4000);
SET @query = 'SELECT * FROM ' +
ISNULL(QUOTENAME(PARSENAME(@tablename, 3), DB_NAME()) + '.', '') +
QUOTENAME(ISNULL(PARSENAME(@tablename, 2), 'dbo')) + '.' +
QUOTENAME(PARSENAME(@tablename, 1)) +
' WHERE invoiceDate >= ''' + CONVERT(varchar(8), @date, 112) + ''''
EXEC(@query)
GO --end of proc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 10, 2015 at 2:42 pm
ScottPletcher (6/10/2015)
Sorry, I was being lazy and didn't include checks for db name and schema:
ALTER PROCEDURE sampleprocedure
@tablename AS varchar(4000),
@date AS date
AS
SET NOCOUNT ON;
DECLARE @query nvarchar(4000);
SET @query = 'SELECT * FROM ' +
ISNULL(QUOTENAME(PARSENAME(@tablename, 3), DB_NAME()) + '.', '') +
QUOTENAME(ISNULL(PARSENAME(@tablename, 2), 'dbo')) + '.' +
QUOTENAME(PARSENAME(@tablename, 1)) +
' WHERE invoiceDate >= ''' + CONVERT(varchar(8), @date, 112) + ''''
EXEC(@query)
GO --end of proc
Don't really need the PARSENAME:
select replace(quotename('dbname.dbo.tablename'),'.','].[')
select replace(quotename('dbo.tablename'),'.','].[')
select replace(quotename('tablename'),'.','].[')
Of course, your code takes into account this: DBName..TableName where the .. will then be interpreted as DBName.dbo.TableName.
June 10, 2015 at 2:52 pm
Lynn Pettis (6/10/2015)
ScottPletcher (6/10/2015)
Sorry, I was being lazy and didn't include checks for db name and schema:
ALTER PROCEDURE sampleprocedure
@tablename AS varchar(4000),
@date AS date
AS
SET NOCOUNT ON;
DECLARE @query nvarchar(4000);
SET @query = 'SELECT * FROM ' +
ISNULL(QUOTENAME(PARSENAME(@tablename, 3), DB_NAME()) + '.', '') +
QUOTENAME(ISNULL(PARSENAME(@tablename, 2), 'dbo')) + '.' +
QUOTENAME(PARSENAME(@tablename, 1)) +
' WHERE invoiceDate >= ''' + CONVERT(varchar(8), @date, 112) + ''''
EXEC(@query)
GO --end of proc
Don't really need the PARSENAME:
select replace(quotename('dbname.dbo.tablename'),'.','].[')
select replace(quotename('dbo.tablename'),'.','].[')
select replace(quotename('tablename'),'.','].[')
Of course, your code takes into account this: DBName..TableName where the .. will then be interpreted as DBName.dbo.TableName.
But what if they enter the delimiters themselves:
select replace(quotename('[dbname].[dbo].[nonstandard table name]'),'.','].[')
Edit: added sql tags around select.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 10, 2015 at 3:18 pm
ScottPletcher (6/10/2015)
...
Try testing your code passing in a value like '[DBName].[SchemaName].[TableName]'. When I use your co0de with something like that the database name is dropped.
June 10, 2015 at 3:26 pm
It just has the QUOTENAME and ISNULL mixed up for the database name. Flip them and it will work. As is it's trying to pass DB_NAME() as the quote character to QUOTENAME, and passing an invalid quote character just makes it output NULL.
Cheers!
June 10, 2015 at 3:35 pm
Attached is another way to accomplish the task. It also includes the code for the dbo.DelimitedSplit8K function.
Sorry, work isn't allowing me to post the code directly.
June 10, 2015 at 3:39 pm
Jacob Wilkins (6/10/2015)
It just has the QUOTENAME and ISNULL mixed up for the database name. Flip them and it will work. As is it's trying to pass DB_NAME() as the quote character to QUOTENAME, and passing an invalid quote character just makes it output NULL.Cheers!
Actually, I think is a bit more than that. Not my code so I am not going to debug it. I'll leave that to Scott since he wrote it.
June 10, 2015 at 3:40 pm
ALTER PROCEDURE sampleprocedure
@tablename AS varchar(4000),
@date AS date
AS
SET NOCOUNT ON;
DECLARE @query nvarchar(4000);
SET @query = 'SELECT * FROM ' +
ISNULL(QUOTENAME(PARSENAME(@tablename, 3)) + '.', '') +
ISNULL(QUOTENAME(PARSENAME(@tablename, 2)) + '.', '') +
QUOTENAME(PARSENAME(@tablename, 1)) +
' WHERE invoiceDate >= ''' + CONVERT(varchar(8), @date, 112) + ''''
PRINT @query
EXEC(@query)
GO --end of proc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 10, 2015 at 3:48 pm
ScottPletcher (6/10/2015)
...
Can't even post your code in a reply, that sucks.
Any way, I would still prefer to use EXEC sys.sp_executesql and pass the variable data using the proper data types and only have to deal with that part of the dynamic SQL that really needs to be done dynamically, which in this case is the name of the table from which data is to be selected.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply