Solved

dynamic sql and sql_variant

Posted on 2007-12-05
5
320 Views
Last Modified: 2010-03-19
it isn't liking me concatening the sql_variant... in my app i have a dropdown list of fields, and they are tagged with their sqldbtypes... and i got everything handled over there if i can get this part of the query working...  i'm not sure i can just cast the sql_variant to an nvarchar and the query will still work as i expect it to...   i hope it will work for fields that are bits, ints, datetimes, nvarchars, etc....  

As you can guess... @value is the sql_variant...


            Set @command =
                  'Select Row_Number() Over(ORDER BY nameCompany) as Row,
                              addrLine1,
                              addrLine2,
                              addrCity,
                              addrCountryCode,
                              addrPostalCode,
                              addrState,
                              keyCompany,
                              nameCompany,
                              othNotes,
                              phoneFax,
                              phoneVoice1,
                              phoneVoice2,
                              sysActive
            Into #Temp
            From Company
            Where ' + @fieldName + '= ' + @value;

            exec(@command)

Tony
0
Comment
Question by:picsnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20417355
just put an explicit cast in there to nvarchar
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20417358
i.e.

            Where ' + @fieldName + '= ' + cast(@value as nvarchar(100));

100 or as large as you need it to be
0
 
LVL 1

Author Comment

by:picsnet
ID: 20417590
will that not change the meaning if @value is a datetime, or int, or float, etc....
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20417607
It finally clicks... then your dynamic SQL is incorrect from the start.
If @value is a string, say 'ABC', then the generated SQL is

                  Select Row_Number() Over(ORDER BY nameCompany) as Row,
                              addrLine1,
                              ....
                              sysActive
            Into #Temp
            From Company
            Where addrLine1 = ABC

Can you see the problem? ABC is not in quotes, and will cause an error.  Try this instead.

            Where ' + @fieldName + '= ''' + replace(convert(nvarchar(100), @value), '''', '''''') + '''';
0
 
LVL 2

Expert Comment

by:dhruvitrivedi
ID: 20418343

if you assign a variable
 @value = ''' + cast(@value as nvarchar(100)) + ''''

and then use it in your query...it may work
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question