Dynamic SQL
-
Tuesday, January 29, 2013 1:04 PM
Hi!
I got an example of Dynamic SQL, but it does not work.
The aim is to create a table with one column for each vendor. The table I use is OCRD and the field is CardName (Vendor's Name)
Here's the script:
DECLARE @DynamicSQL varchar(8000)
IF OBJECT_ID('Test') IS NOT NULL
DROP TABLE Test
SET @DynamicSQL = 'CREATE TABLE Test ('
SELECT @DynamicSQL = @DynamicSQL + '[' + CardName + '] bit,'
FROM OCRD
WHERE CardName LIKE 'AL%'
SET @DynamicSQL = @DynamicSQL + ')'
----- NO ERRORS UP TO HERE -----
EXEC (@DynamicSQL)
SELECT * FROM Test
/* ERROR MSG:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 208, Level 16, State 1, Line 23
Invalid object name 'Test'.*/
I Expect the contents of the SQL String to be something like this:
CREATE TABLE Test ([Allan Smith] bit, [Alexander Shipping] bit, [Aluminium Co Ltd] bit, )
And I Expect the result set to look like this:
| Allan Smith | Alexander Shipping | Aluminium Co Ltd |
MY QUESTIONS:
(a) Where is the problem?
(b) What is 'bit'. If it is a datatype, it means 0 or 1. So why use it here?
Thanks
Leon Lai Kan
- Edited by Leon Lai Tuesday, January 29, 2013 1:12 PM
All Replies
-
Tuesday, January 29, 2013 1:09 PM
WHERE
CardName LIKE 'AL1%'Isn't going to get you any of those 3 records you listed
Bit is a datatype that is 0 or 1 - most folks do not use it very frequently
Chuck Pedretti | Magenic – North Region | magenic.com
- Edited by Chuck Pedretti Tuesday, January 29, 2013 1:10 PM
- Marked As Answer by Leon Lai Tuesday, January 29, 2013 2:25 PM
-
Tuesday, January 29, 2013 1:10 PM
a) What does a PRINT @DynamicSQL return?
b) How could we know that? You have chosen this data type. It should depend on your pivot problem.
-
Tuesday, January 29, 2013 1:12 PM
CHK THIS
DECLARE @DynamicSQL varchar(8000)
IF OBJECT_ID('Test') IS NOT NULL
DROP TABLE Test
SET @DynamicSQL = 'CREATE TABLE Test ('
SELECT @DynamicSQL = @DynamicSQL + '[' + CardName + '] bit,'
FROM OCRD
WHERE CardName LIKE 'AL1%'
SET @DynamicSQL = SUBSTRING(@DynamicSQL,1,LEN(@DynamicSQL)-1) + ')'
----- NO ERRORS UP TO HERE -----
EXEC (@DynamicSQL)
SELECT * FROM TestPlease have look on the comment
- Marked As Answer by Leon Lai Tuesday, January 29, 2013 1:44 PM
-
Tuesday, January 29, 2013 1:12 PM
Hi Chuck
Thanks for your reply.
It's a typing error.
Should be:
CardNameLIKE'AL%'
Best Regards
Leon
-
Tuesday, January 29, 2013 1:12 PM
The better syntax is SELECT ColumnName INTO <NewTableName> FROM <OldTableName>.
The other issue you are trying to handle is transposing Rows in your source Table into Columns in the Destination Table (PIVOT). How many vendors do you have. Can you support that many columns per table. If you give the exact requirement, probably we can come up with a better solution.
For the BIT Question, Yes BIT is a datatype which can have the 3 values 1, 0 and NULL. Bit is generally used to denote a on/off state and nothing more. If you have more than on/off state then you should probably use a TINYINT/SMALLINT/INT/BIGINT depending upon the number of values you want to store.
- Edited by Murali P BMicrosoft Employee Tuesday, January 29, 2013 1:14 PM Typos
-
Tuesday, January 29, 2013 1:13 PM
The error occurs when there are no results from the query and your dynamic query looks like this:
CREATE TABLE Test ()Chuck Pedretti | Magenic – North Region | magenic.com
- Proposed As Answer by Gavin Campbell Tuesday, January 29, 2013 1:21 PM
-
Tuesday, January 29, 2013 1:20 PM
Hi Stephan.
Thanks for your response.
It's not a real SQL.
I got this script from a book and want to try it for learning. I modified a few things to adapt to my database. But it does not work.
I don't know why the author used "bit".
a) What does a PRINT @DynamicSQL return?
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
CREATE TABLE Test ()
Best Regards
Leon
-
Tuesday, January 29, 2013 1:21 PM
Hi Stephan.
Thanks for your response.
It's not a real SQL.
I got this script from a book and want to try it for learning. I modified a few things to adapt to my database. But it does not work.
I don't know why the author used "bit".
a) What does a PRINT @DynamicSQL return?
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
CREATE TABLE Test ()
Best Regards
Leon
It didn't work because of the typo in your where clause which resulted in no records being returned. So you wind up with a create table statement that has no fields. What book are you looking at? That has got to be one of the most useless examples I've ever seen.Chuck Pedretti | Magenic – North Region | magenic.com
-
Tuesday, January 29, 2013 1:27 PM
Hi Santosh
WOW! It works wonderfully! I guess I understand your logic of -1.
Just please explain to me what is the reason for using "bit" as datatype.
I will close the thread as answered in a moment.
Thanks a lot
Leon
PS
Please have look on the comment
I don't see any comment
ADDED
Don't bother. I replaced "bit" by "nVarChar", and it still works.
But it's very mysterious why it should work witt "bit". If you find out, just send a message.
CLOSING ... and many thanks
-
Tuesday, January 29, 2013 2:25 PM
Hi Chuck
I reworked all the replies I received, including yours.
This is one way I learn from my mistakes.
It turns out that your answer was correct, and that AL1% is not simply a typing mistake.
I happened to have no vendor beginning with 'AL1'.
So, that's why I got the error message.
Replacing it by "AL" makes the script work perfectly.
I hereby correct my mistake and mark your reply as CORRECT also!
I did not expect the script to return an error. A normal query would not have returned an error, only empty row.
Best Regards,
Leon
-
Tuesday, January 29, 2013 3:04 PM
Closed as the thread may be - I agree with Chuck, bad book, bad example.
Creating tables dynamically may look cool, but it's usually wrong.
I have an article on my web site where I discuss dynamic SQL in detail. How to use, when to use it - and when not.
http://www.sommarskog.se/dynamic_sql.html
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
.png)
