Solved

dynamic sql and sql_variant

Posted on 2007-12-05
5
302 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
  • 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now