Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aax
Starting Member
11 Posts |
 Posted - 2005-08-31 : 22:26:10
|
| I have a stored procedure and when doing a select @varYear_2_TypeCode at the bottom of the page I receive a numeric value of 6 which is correct. But when I execute @sql I receive this error. I know that @varYear_2_TypeCode is declared. Any ideas why I am receiving this error?Error: Must declare the variable '@varYear_2_TypeCode'.----------------------------------declare @varYear_2_TypeCode int-- Year_2 declare target_cursor CURSOR FOR select a.column_name, a.type_code, a.field_id from lnr_fields a inner join lnr_sub_pub_field_rel b on a.field_id = b.field_id where a.column_name = 'vm.year' and a.field_id = 12 and b.sub_publication_id = @I_SubPubID order by b.field_order Open target_cursor Set @varYear_2 = '' Fetch Next From target_cursor Into @cName, @typeCode, @field_id While @@FETCH_STATUS = 0 Begin If len(@varYear_2) > 0 Begin Set @varYear_2 = @varYear_2 + ' + '''' + ' End Set @varYear_2_TypeCode = @typeCode --Set @varYear_2_TypeCode = '' + cast(@typeCode as varchar(15)) + '' Set @varYear_2 = 'Right(isnull(cast(' + @cName + ' as varchar(25)),''''), 2)' Fetch Next From target_cursor Into @cName, @typeCode, @field_id End Close target_cursor Deallocate target_cursor -- ' + case when @varYear_4 = '' then '''''' else @varYear_4 end + ' + '''' + @varYear_2_TypeCode + ''|'' + ' + case when @varYear_2 = '' then '''''' else @varYear_2 end + ' + ''|6|'' + ' + case when @varMake = '' then '''''' else @varMake end + ' + ''|6|'' + ' + case when @varModel = '' then '''''' else @varModel end + ' + ''|6|'' + ' + case when @varSeries = '' then '''''' else @varSeries end + ' + ''|6|'' + ' + case when @varBodyStyle = '' then '''''' else @varBodyStyle end + ' + ''|6|'' + ' + case when @varVIN_Full = '' then '''''' else @varVIN_Full end + ' + ''|7|'' + ' + case when @varVIN_Last8 = '' then '''''' else @varVIN_Last8 end + ' + ''|5|'' + ' + case when @varMiles = '' then '''''' else @varMiles end + ' + ''|4|'' + ' + case when @varRetailPrice = '' then '''''' else @varRetailPrice end + ' + ''|4|'' + ' + case when @varInternetPrice = '' then '''''' else @varInternetPrice end + ' + ''|6|'' + ' + case when @varStockNum = '' then '''''' else @varStockNum end + ' as linerTextexecute (@sql) --select @varYear_2_TypeCode |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-01 : 00:55:20
|
The @varYear_2_TypeCode you decalred at the top will not be in scope inside the execute (@sql)statement.You'll need to find another way to get it back out. The most likely candidate is sp_ExecuteSQL - it may just work as-is with your existing @sql string, something along the lines of:EXEC sp_ExecuteSQL @sql, '@varYear_2_TypeCode int OUTPUT', @varYear_2_TypeCode int OUTPUTKristen |
 |
|
|
aax
Starting Member
11 Posts |
Posted - 2005-09-01 : 10:03:38
|
| This didn't work. Incorrect Syntax near intEXEC sp_ExecuteSQL @sql, '@varYear_2_TypeCode int OUTPUT', @varYear_2_TypeCode int OUTPUT Any other ideas?? |
|
|
|
aax
Starting Member
11 Posts |
Posted - 2005-09-01 : 10:25:24
|
| I got it to work by changing @varYer_2_TypeCode to varchar(100) and adding this: Set @varYear_2_TypeCode = '''' + cast(@typeCode as varchar(15)) + '|'' + ' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-01 : 10:52:34
|
"This didn't work"Sorry, sloppy typing, I've changed my original, but on rechecking your code I see you are not actually trying to evaluate that variable in your EXEC statement, and your fix is the one you need!Best bet for debugging this type of thing is to put SELECT @sqljust before yourexecute (@sql)and cut & paste into Query Analyser and see if it will execute it. If not you can sort out the syntax in Q.A., and then replicate the changes to your dynamic SQL. The number of nested quotes and stuff becomes a nightmare in the dynamic stuff ...  Kristen |
|
|
|
aax
Starting Member
11 Posts |
Posted - 2005-09-01 : 12:52:41
|
| I got that to work but now I need to not show 6|| in the dynamic sql if @varModel_TypeCode is Null otherwise show the value of: @varModel_TypeCode|@varModel| I currently have the 6 hardcoded and it works but I need it to be the value of @varModel_TypeCode. ' + case when @varModel_TypeCode = '' then '''''' else + '''6|''' end + ' + '''' + ' + case when @varModel = '' then '''''' else @varModel end + ' + ''|'' + |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-01 : 13:39:37
|
| If I've understoof correctly this should do the trick:COALESCE(@varModel_TypeCode + '|' + @varModel + '|', '''6|''')Maybe you need the quotes too:COALESCE('''' + @varModel_TypeCode + '|' + @varModel + '|' + '''', '''6|''')Kristen |
|
|
|
aax
Starting Member
11 Posts |
Posted - 2005-09-01 : 13:46:30
|
| Thanks. |
|
|
|
|
|
|
|
|
Stored Procedure & target_cursor