December 18, 2015 at 4:09 pm
I have a stored procedure in which I need to set the parameter equal to a column in a table. I have tried @parameter = select distinct column_name from table_name but it is throwing an error about the syntax being incorrect after the = sign. What is the issue with my syntax?
December 21, 2015 at 7:31 am
pherman777 (12/18/2015)
I have a stored procedure in which I need to set the parameter equal to a column in a table. I have tried @parameter = select distinct column_name from table_name but it is throwing an error about the syntax being incorrect after the = sign. What is the issue with my syntax?
The syntax for setting the value of a parameter with a select statement is like this.
select @parameter = Column
from Table
where SomeCondition
In your example why the distinct? What you have is a scalar variable and the distinct in there has a particular code smell that indicates you might get more than 1 row returned. I would recommend a query that ensures you get 1 and only 1 row. An aggregate, maybe a group by, top 1 with an order etc...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2015 at 8:07 am
Does this also work when executing the stored procedure with hard-coded values? The number of possible equipment class values is taking a long time to execute which is why I am trying to use hard-coded values with values from a table with only two columns. See my entire exec statement.
Exec DWH.spAMBSiteAssetCountReport @MaintenanceLocation=N'366,367,332,362,3,360,331,365,361,364,357,396,2,406,371,4,368,369,370,333,394,358,359,395,355,353,354,335,363,356,397,352,349,348,351,350,347,372,373,374,377,375,376,382,386,383,387,384,389,381,391,378,385,379,380,388,390',@FLClassDescription=N'3,4,5,2,1',@EquipmentClass = EquipmentClass,@AssetStatus=N'All'
December 21, 2015 at 8:22 am
Sean's code is right. The reason your code didn't work was you missed out the parentheses:
SET @parameter = (select distinct column_name from table_name)
You won't get a syntax error for the above, but you'll get a different kind of error if the subquery returns more than one row, as Sean pointed out.
John
December 21, 2015 at 8:58 am
pherman777 (12/21/2015)
Does this also work when executing the stored procedure with hard-coded values? The number of possible equipment class values is taking a long time to execute which is why I am trying to use hard-coded values with values from a table with only two columns. See my entire exec statement.
Exec DWH.spAMBSiteAssetCountReport @MaintenanceLocation=N'366,367,332,362,3,360,331,365,361,364,357,396,2,406,371,4,368,369,370,333,394,358,359,395,355,353,354,335,363,356,397,352,349,348,351,350,347,372,373,374,377,375,376,382,386,383,387,384,389,381,391,378,385,379,380,388,390',@FLClassDescription=N'3,4,5,2,1',@EquipmentClass = EquipmentClass,@AssetStatus=N'All'
This might work but it means you have to have @MaintenanceLocation defined as a varchar large enough to hold those values. And I doubt it is going to do what you think. You would need to parse that into values again before you would be able to use it. You can see the link in my signature about splitting a string. Another (and probably better) option here would be to pass a table valued parameter instead of a big delimited list like that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2015 at 9:22 am
I am using a function to split the strings on all parameter values and the query does work when I input hard-coded values. The issue is it is taking up to 4 minutes to execute the query so I'm really trying to optimize my time here.
December 21, 2015 at 9:26 am
pherman777 (12/21/2015)
I am using a function to split the strings on all parameter values and the query does work when I input hard-coded values. The issue is it is taking up to 4 minutes to execute the query so I'm really trying to optimize my time here.
My guess is the function being used to split the string is horribly inefficient. Can you post that function?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2015 at 12:17 pm
OK. I got the values to come in using a comma delimited list. The execution time was a matter of seconds but now the issue is I need to pass the results of a count of distinct equipment classes to the @EquipmentClass parameter. I have added a column to get the count of distinct values but how do I pass the count value into the parameter? Do I need to use the split function if I am only using a count value and not a list of hard-coded values? See below:
Exec DWH.spAMBSiteAssetCountReport @MaintenanceLocation=N'366,367,332,362,3,360,331,365,361,364,357,396,2,406,371,4,368,369,370,333,394,358,359,395,355,353,354,335,363,356,397,352,349,348,351,350,347,372,373,374,377,375,376,382,386,383,387,384,389,381,391,378,385,379,380,388,390',@FLClassDescription=N'3,4,5,2,1',@EquipmentClass = AssetCount ,@AssetStatus=N'All'
December 21, 2015 at 12:36 pm
pherman777 (12/21/2015)
OK. I got the values to come in using a comma delimited list. The execution time was a matter of seconds but now the issue is I need to pass the results of a count of distinct equipment classes to the @EquipmentClass parameter. I have added a column to get the count of distinct values but how do I pass the count value into the parameter? Do I need to use the split function if I am only using a count value and not a list of hard-coded values? See below:
Exec DWH.spAMBSiteAssetCountReport @MaintenanceLocation=N'366,367,332,362,3,360,331,365,361,364,357,396,2,406,371,4,368,369,370,333,394,358,359,395,355,353,354,335,363,356,397,352,349,348,351,350,347,372,373,374,377,375,376,382,386,383,387,384,389,381,391,378,385,379,380,388,390',@FLClassDescription=N'3,4,5,2,1',@EquipmentClass = AssetCount ,@AssetStatus=N'All'
How do we know?? So far all you have posted is some incredibly cryptic stuff with basically no substance at all. You have to provide some detail if you want help. It seems you are more willing to post some information on another forum where you have asked pretty much the same thing. http://stackoverflow.com/questions/34402576/passing-the-results-of-a-query-to-a-parameter-in-a-stored-procedure Give us some information and we can help you with a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2015 at 12:46 pm
The reason I am posting on multiple sites is that I am under a deadline (as is the case for most of us). I don't believe it is required to only use one site for help. I do appreciate the feedback and the help however.
I resolved the issue by assigning the AssetCount value in a string i.e. @EquipmentClass = N'AssetCount' which populated my query with distinct values from the original query. Thanks for the suggestions and the help. It was very useful.
December 21, 2015 at 12:49 pm
pherman777 (12/21/2015)
The reason I am posting on multiple sites is that I am under a deadline (as is the case for most of us). I don't believe it is required to only use one site for help. I do appreciate the feedback and the help however.I resolved the issue by assigning the AssetCount value in a string i.e. @EquipmentClass = N'AssetCount' which populated my query with distinct values from the original query. Thanks for the suggestions and the help. It was very useful.
I did not suggest you shouldn't use only 1 forum. I was trying to point out that when using ANY online forum you need to provide details for the people trying to help. In both locations I saw you did not provide enough information. This is even more critical when as you say you are under a deadline. With the required information people can actually work on your issue instead of trying to extract the entire problem. Glad you got a solution and hopefully it works well for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply