Stored procedures with booleans leading to different data types.
-
I am using Visual Studio 2010 Pro to make a report. I am working off someone else's stored procedure that I am not allowed to modify. The problem is the stored procedure has IF statements, by way of example: IF (criteria_1 = 'A') BEGIN SELECT col_for_a_is_varchar100 FROM some_table_1 END IF (criteria_1 = 'B') BEGIN SELECT col_for_b_is_varchar50 FROM some_table_1 END My problem is that in VS I have to use the name of column in the table but depending on what criteria_1 is equal to I could have a different column name: col_for_a_is_varchar100 vs. col_for_b_is_varchar50. I don't think there will be a problem with one being a VARCHAR(50) and the other a VARCHAR(100). I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working. Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work. If anyone could help me out it would save me from this grunt work and let me get back to coding in TeraData SQL and SAS again.
-
I am using Visual Studio 2010 Pro to make a report. I am working off someone else's stored procedure that I am not allowed to modify. The problem is the stored procedure has IF statements, by way of example: IF (criteria_1 = 'A') BEGIN SELECT col_for_a_is_varchar100 FROM some_table_1 END IF (criteria_1 = 'B') BEGIN SELECT col_for_b_is_varchar50 FROM some_table_1 END My problem is that in VS I have to use the name of column in the table but depending on what criteria_1 is equal to I could have a different column name: col_for_a_is_varchar100 vs. col_for_b_is_varchar50. I don't think there will be a problem with one being a VARCHAR(50) and the other a VARCHAR(100). I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working. Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work. If anyone could help me out it would save me from this grunt work and let me get back to coding in TeraData SQL and SAS again.
-
I am using Visual Studio 2010 Pro to make a report. I am working off someone else's stored procedure that I am not allowed to modify. The problem is the stored procedure has IF statements, by way of example: IF (criteria_1 = 'A') BEGIN SELECT col_for_a_is_varchar100 FROM some_table_1 END IF (criteria_1 = 'B') BEGIN SELECT col_for_b_is_varchar50 FROM some_table_1 END My problem is that in VS I have to use the name of column in the table but depending on what criteria_1 is equal to I could have a different column name: col_for_a_is_varchar100 vs. col_for_b_is_varchar50. I don't think there will be a problem with one being a VARCHAR(50) and the other a VARCHAR(100). I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working. Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work. If anyone could help me out it would save me from this grunt work and let me get back to coding in TeraData SQL and SAS again.
D Hoffman wrote:
I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working.
Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work.There would be a problem for taking two different name for two different conditions... problem is if you want to show both values in one report it will show only one label field. For this you can produce another label field and suppress it depending upon the condition the query returning. The best i would suggest you to produce same name in both cases and you can write convert() for converting to Varchar(100) and Varchar(50).
-
I am using Visual Studio 2010 Pro to make a report. I am working off someone else's stored procedure that I am not allowed to modify. The problem is the stored procedure has IF statements, by way of example: IF (criteria_1 = 'A') BEGIN SELECT col_for_a_is_varchar100 FROM some_table_1 END IF (criteria_1 = 'B') BEGIN SELECT col_for_b_is_varchar50 FROM some_table_1 END My problem is that in VS I have to use the name of column in the table but depending on what criteria_1 is equal to I could have a different column name: col_for_a_is_varchar100 vs. col_for_b_is_varchar50. I don't think there will be a problem with one being a VARCHAR(50) and the other a VARCHAR(100). I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working. Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work. If anyone could help me out it would save me from this grunt work and let me get back to coding in TeraData SQL and SAS again.
D Hoffman wrote:
The problem is the stored procedure has IF statements
Having an
IFisn't a problem, returning different views based on the parameters is. Adding another SP sounds like the best way to go. What's there prohibiting the creation of a new stored procedure?Bastard Programmer from Hell :suss:
-
D Hoffman wrote:
The problem is the stored procedure has IF statements
Having an
IFisn't a problem, returning different views based on the parameters is. Adding another SP sounds like the best way to go. What's there prohibiting the creation of a new stored procedure?Bastard Programmer from Hell :suss:
Solved the issue. Parameters can be specified that don't show up in the list of preview items. If these parameters are returned (the IF conditions are met and the different view is instead displayed) then these parameters have the requisite value, otherwise they don't and aren't applicable. Thank you for the attempts to answer my question but they were all incorrect.