Solved

dynamic sql and sql_variant

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 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