?
Solved

dynamic sql and sql_variant

Posted on 2007-12-05
5
Medium Priority
?
326 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 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