April 23, 2012 at 2:54 am
Hello,
Please edit the following line and explain what is the problem
Declare @MyColumn nvarchar(50)
set @AddItemVal = (SELECT @MyColumn from myTable Where Column2 = @Myval)
the Error Msg:
Conversion failed when converting the nvarchar value 'AddIt' to data type int.
Notice: the 'AddIt' is a part of the original Column Name
Regards
April 23, 2012 at 3:07 am
Please post the complete query and the exact error message that you are receiving
The query you have provided is not enough.
You are declaring "@MyColumn" and using "@AddItemVal" and "@Myval" which have not been declared.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 23, 2012 at 3:08 am
More information would be really, really helpful!
What is the column type of ?AddItemVal?
What is the column type of @MyVal?
What is the column type of Column2
Do have some sample data?
Was this statement working previously and it's now stopped, or intermittent?
We're happy to try and help but can only do so with complete, or near as complete information.
Thank you.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 3:19 am
it works when I write the coulmn name instead of the variable
the '@AddItemVal' is declared as nvarchar
'@Myval' is nvarchar
Column2 is nvarchar
@MyColumn is nvarchar
the problem happens only when I use @MyColumn instead of AddItem
@MyColumn gets its value from a parameter
AddItem is the Column Name in myTable
All what I want to do is to make the coulmn name (AddItem) dynamic so I can use the same procedure to make query on any column of MyTable
April 23, 2012 at 3:38 am
There is no easy way to do what you're looking to do. Too bad, I'd love to do this myself.
The only way to query out columns dynamically would be to (you guessed it) use dynamic SQL. Assigning the result to your local variable then becomes problematic.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 23, 2012 at 3:49 am
Build your query string dynamically, then execute it with sp_executesql. If you use an output parameter, you can get the result back to the scope of the calling batch.
John
April 23, 2012 at 4:23 am
can you please tell me how to do it ?
I tried it many times but I got many errors
thanks
April 23, 2012 at 4:28 am
http://msdn.microsoft.com/en-us/library/ms188001.aspx
If you want more detailed help, post the code and post the error messages.
John
April 23, 2012 at 4:41 am
John,
That's exactly where I looked! And I can read and construct a working example too! 🙂
DECLARE @SQL nvarchar(500)
DECLARE @AddItemVal INT
DECLARE @MyColumn NVARCHAR(25)
DECLARE @ParmDefinition nvarchar(500)
CREATE TABLE #myTable (Col1 INT, Col2 INT, result INT)
INSERT INTO #myTable
SELECT 1, 2, 3
UNION ALL SELECT 3, 4, 5
UNION ALL SELECT 6, 7, 8
SET @MyColumn = 'Col2'
SET @SQL = N'SELECT @AddItemVal = ' + @MyColumn + ' FROM #myTable WHERE Col2 = @MyVal'
SET @ParmDefinition = N'@MyVal int, @AddItemVal nvarchar(25) OUTPUT';
EXECUTE sp_executesql @SQL, @ParmDefinition, @MyVal = 2, @AddItemVal = @AddItemVal OUTPUT;
SELECT @AddItemVal;
DROP TABLE #myTable
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 23, 2012 at 4:55 am
Declare
@table varchar(30),
@col varchar(max),
@query varchar(max)
Set @table = 'tablename'
Set @col = 'col1, col2, col3, col4'
Set @query = 'Select '+@col+' From '+@table+';'
Exec (@query)
April 23, 2012 at 5:21 am
Thanks dwain.c
But I cannot create a table, its a Web Application and many users use it at the same time
and it will slow down my application too
April 23, 2012 at 5:24 am
Dwain's not suggesting you create a table. He's creating a table purely so that he can test the code and show you how the code works since you did not post any table definitions.
If you'd posted a table definition, he'd have used your table name. Since you didn't, he made one up.
Replace #myTable with whatever your table name is and the example column names with whatever your columns are.
Just note that this is not a good design and it's got some nasty security holes. Read up on SQL Injection before you put something like this into a web app.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2012 at 6:13 am
Thanks guys for help I will test it out and give a reply soon
GilaMonster,
After our last discussion I started to read some about the SQL Injection
I think using the dynamic SQLs with a plus mark (+) and qoutes is always dangrous So I have to learn more about the sql functions to avoid these risks correctly
If you know a good books to learn SQL Server codes and the SQL Injections please tell me about then
Thanks
April 23, 2012 at 6:14 am
Try this: http://www.sommarskog.se/dynamic_sql.html
John
April 23, 2012 at 6:21 am
Thanks John it looks a great tutorial I will read it 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply