August 13, 2010 at 10:09 am
DECLARE @month char(10)
SET @month=(select distinct CASE
When July.F1007 is null or July.F1007 < 1 then 'MovementEXP.F1007'
Else 'July.F1007'
END from movementexp join july on MovementEXP.F01=July.F01)
Later I want to use @month in a command but get an "error converting datatype varchar to numeric" with the following:
field1 * @month as GrossWineryAmt
@month is July.F1007. However, if I use - field1 * July.F1007 as GrossWineryAmt - it works. So it seems my variable is not treated as a field name but just as text. Is there a way for it to be evaluated as a field name? BTW, July.F1007 is numeric(9,3).
August 13, 2010 at 10:28 am
if its numeric then what do you mean by
Else 'July.F1007'.
not sure but could be the reason.
----------
Ashish
August 13, 2010 at 10:29 am
Also if you expecting the integer value to be return then why you creating your @..... as char type?
----------
Ashish
August 13, 2010 at 10:33 am
Robert Lassiter (8/13/2010)
DECLARE @month char(10)SET @month=(select distinct CASE
When July.F1007 is null or July.F1007 < 1 then 'MovementEXP.F1007'
Else 'July.F1007'
END from movementexp join july on MovementEXP.F01=July.F01)
Later I want to use @month in a command but get an "error converting datatype varchar to numeric" with the following:
field1 * @month as GrossWineryAmt
@month is July.F1007. However, if I use - field1 * July.F1007 as GrossWineryAmt - it works. So it seems my variable is not treated as a field name but just as text. Is there a way for it to be evaluated as a field name? BTW, July.F1007 is numeric(9,3).
Ok, its a bit tricky to figure out what you want here. No matter what you want, there are some mistakes.
As currently written, @month will either be equal to the string ''July.F1007' or 'MovementEX' since it is a char(10) and you are assigning more than ten characters. Is that what you want, or do you want @month to hold the number from one of those fields. If you want the number, then take the single quotes off from 'MovementEXP.F1007' and 'July.F1007' and declare @month as numeric(9,3)
If you WANT @month to hold the fieldname, then later want to query using that fieldname, you'll have to use dynamic sql to build the query and execute it.
August 13, 2010 at 10:40 am
Part of the problem is that your question is very difficult to read and to understand what you are trying to do.
If I understand correctly you want to create a variable of type CHAR and store a field name in it. Then you want to use that variable in a query later to specify which field to use.
field1 * @month as GrossWineryAmt
So you are expecting this to resolve as either
field1 * July.F1007 as GrossWineryAmt
or
field1 * MovementEXP.F1007 as GrossWineryAmt
Correct? If so you are going about this incorrectly. Instead of a variable just put your case statement directly into your query. So your code would instead by
field1 * CASE WHEN July.F1007 is null or July.F1007 < 1 THEN MovementEXP.F1007
ELSE July.F1007 END AS GrossWineryAmt
Your only other option is dynamic SQL and this is a MUCH better option.
Hope that helps.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 13, 2010 at 4:37 pm
Is there a way for it to be evaluated as a field name?
No, not in SQL Server.
SQL does not allow db names, table names, column names or keywords to be variables.
Scott Pletcher, SQL Server MVP 2008-2010
August 13, 2010 at 10:21 pm
scott.pletcher (8/13/2010)
Is there a way for it to be evaluated as a field name?
No, not in SQL Server.
SQL does not allow db names, table names, column names or keywords to be variables.
Thank you for saving me a lot of time trying to do the impossible. I will heed the advice of other respondents and try out their suggestions.
August 14, 2010 at 1:50 am
scott.pletcher (8/13/2010)
Is there a way for it to be evaluated as a field name?
No, not in SQL Server.
SQL does not allow db names, table names, column names or keywords to be variables.
Not completely true, you can do it using dynamic SQL to create the sql command to execute, however, in this case that is not the way to go. i would use the advice of others in this thread.
August 16, 2010 at 8:07 am
Not completely true, you can do it using dynamic SQL to create the sql command to execute, however, in this case that is not the way to go. i would use the advice of others in this thread.
You can use dynamic SQL, but then you are not still having SQL parse a table name, for example, from a variable. Try it, you'll see, it won't work.
EXEC sp_executesql N'SELECT TOP 1 * FROM @tablename', N'@tablename sysname', N'yourtablename'
SQL expects @tablename to be a table variable, NOT a placeholder for a permanent [ or #temp] table name.
Unlike Oracle, for example, which can use variables for any part of the statement, and the dbms itself will properly substitute for them at run time,
SQL server does not have that capability.
Scott Pletcher, SQL Server MVP 2008-2010
August 16, 2010 at 8:27 am
scott.pletcher (8/16/2010)
Not completely true, you can do it using dynamic SQL to create the sql command to execute, however, in this case that is not the way to go. i would use the advice of others in this thread.
You can use dynamic SQL, but then you are not still having SQL parse a table name, for example, from a variable. Try it, you'll see, it won't work.
EXEC sp_executesql N'SELECT TOP 1 * FROM @tablename', N'@tablename sysname', N'yourtablename'
SQL expects @tablename to be a table variable, NOT a placeholder for a permanent [ or #temp] table name.
Unlike Oracle, for example, which can use variables for any part of the statement, and the dbms itself will properly substitute for them at run time,
SQL server does not have that capability.
To achieve the above in SQL Server you can to do something like:
DECLARE @sql NVARCHAR(4000)
--Check if supplied parameter is a table name! (avoiding SQL injection in @tablename)
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = @tablename)
BEGIN
SET @sql = N'SELECT TOP 1 * FROM ' + @tablename
EXEC sp_executesql @sql
END
August 16, 2010 at 8:29 am
But the table name is static, not a variable, as clearly you're passing a literal name to SQL Server.
SQL Server, unlike Oracle, does not have the capability to use variable names for table names, keywords, operators, etc.. In Oracle, for example, you could use a variable to swtich between "UNION" and "UNION ALL" just by assigning the variable that value; that will not work in SQL.
Scott Pletcher, SQL Server MVP 2008-2010
August 16, 2010 at 8:49 am
Kind of. I have just shown how you can pass table name (or column name) in the input parameter (or variable) and use it to build dynamic SQL. You are right, it is not the same as in ORACLE, but still can be used.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply