asked
convert below parameterized procudere into query itself sqlserver 2012
This is a procedure in my application. The task of this procedure is when user gives parameter values from front end. It needs to display result accordingly. Parameters: For parameters @Site From front end window he can give only one parameter value from multiple parameter values in drop down list. site column is there in existing table which is filtered by this parameter in query For parameters @comm_group1, @comm_group2, @productcode, @productfamily He can select a single parameter value or multiple parameter values or if he selects % In front end window for these parameters, all the values of particular column should be select. Like For @comm_group1, if he selects s05 as parameter value need to display result for this column value only or if he selects s05,s06 need to display result for these values of comm_group1 column or if he selects %, need to display result for all values of comm_group1. Comm_group1 column is there in table. This column is filtered by @comm_group1 parameter value in procedure. I used comma separated values into rows function select * from [dbo].[CSVToList] ('so5,so6,s07') in front end, he gives s05,s06,s07 for this parameter which gives separate row values(s05 one row,s06 another row, s07 another row) like 3 rows here comes Now I need to convert this stored procedure into query itself for my new application modified version because it won’t support stored procedures or functions. Give me any suggestions. USE [CMMS_ENB_REPORTS] GO /****** Object: StoredProcedure [dbo].[USP_ENV_StockChanges] Script Date: 5/6/2015 12:45:54 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* --Report Name : Stock Changes(01.04) --Created Date : 2013-11-11 12:00:27.410 --Purpose : -- --Modified Date :2014-05-14 --Purpose : Added previous values */ -- [USP_ENV_StockChanges] 'SLV' ,'%','%' ,'%','%' -- [USP_ENV_StockChanges] '{Site}' , '{COMM_GROUP_1}','{COMM_GROUP_2}','{Product_code}','{Product_family}' ALTER proc [dbo].[USP_ENV_StockChanges] @site nvarchar(5), @comm_group1 nvarchar(10)=null , @comm_group2 nvarchar(10)=null, @productcode nvarchar(10)=null, @productfamily nvarchar(10)=null as if @comm_group1='%' set @comm_group1=null if @comm_group2='%' set @comm_group2=null if @productcode='%' set @productcode=null if @productfamily='%' set @productfamily=null SELECT distinct [SITE] ,[YEAR] ,[MONTH] ,[PERIOD] ,[INVENTORY_UOM] ,[PART_NO] ,[ACCOUNTING_GROUP] ,[ACCOUNTING_GROUP_DESC] ,[ASSET_CLASS] ,[PRODUCT_CODE] ,[PRODUCT_CODE_DESC] ,[PRODUCT_FAMILY] ,[PRODUCT_FAMILY_DESC] ,[COMM_GROUP_1] ,[COMM_GROUP_1_DESC] ,[COMM_GROUP_2] ,[COMM_GROUP_2_DESC] ,[QUANTITY] ,[TOTAL_INVENTORY_VALUE] [CURRENT_TOTAL_INVENTORY_VALUE] ,[TOTAL_INVENTORY_VALUE_$] [CURRENT_TOTAL_INVENTORY_VALUE_$] ,[PRE_TOTAL_INVENTORY_VALUE] [PREVIOUS_TOTAL_INVENTORY_VALUE] ,[PRE_TOTAL_INVENTORY_VALUE_$] [PREVIOUS_TOTAL_INVENTORY_VALUE_$] ,[PART_DESCRIPTION] FROM [dbo].[ENV_StockChanges_V2] Where [site] = @site and ( COMM_GROUP_1 in (SELECT * FROM dbo.CSVToLIst(@comm_group1)) or @comm_group1 is null ) and ( COMM_GROUP_2 in (SELECT * FROM dbo.CSVToLIst(@comm_group2)) or @comm_group2 is null) and ( PRODUCT_CODE in (SELECT * FROM dbo.CSVToLIst(@productcode)) or @productcode is null) and ( PRODUCT_FAMILY in (SELECT * FROM dbo.CSVToLIst(@productfamily)) or @productfamily is null) order by PERIOD
How can your application support queries but not stored procedures?
Every ORM tool I've worked with supports stored procedures.
--I posted answer the same question with currect clarification, sorry for that. see that and this also and give suggestions SELECT A.* FROM OPENROWSET('SQLNCLI11','Server=.;Trusted_Connection=yes;','exec USP_ENV_Value_Per_Part ''SLV'' ,''%'',''%'' ,''%'',''%''') AS A; i run above query it is showing error like this Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because every code path results in an error; see previous errors for some of these. Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 1 Could not find stored procedure 'USP_ENV_Value_Per_Part'.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
hey actually my stored procedure need to execute as derived table and pass the result to outer query. like select column1,column2,column3 -- need to select a few to all columns from openquery( [ENVSVRCMMSBI001],'exec USP_ENV_Value_Per_Part ''SLV'' ,''%'',''%'' ,''%'',''%'''--returning 10 columns by the select query in stored procedure) we can acheive this by inserting the result of stored procedure into temp tabble and do operations on temp table like selecting a few columns but it is performance issue any good suggestion. i run a query like this SELECT * into #tempt FROM OPENQUERY(ENVSVRCMMSBI001, N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = ''SLV'', @comm_group1 in (''%''), @comm_group2 in (''%''), @productcode in (''%''), @productfamily in (''%'') WITH RESULT SETS ( { column_specification} ); '); SELECT * into #tempt FROM OPENQUERY(ENVSVRCMMSBI001, N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = ''SLV'', @comm_group1 = ''%'', @comm_group2 = ''%'', @productcode = ''%'', @productfamily = ''%'' WITH RESULT SETS ( { column_specification} ); '); SELECT * FROM OPENQUERY(ENVSVRCMMSBI001, N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = ''SLV'', @comm_group1 in (''%''), @comm_group2 in (''%''), @productcode in (''%''), @productfamily in (''%'') WITH RESULT SETS ( { column_specification} ); '); SELECT * FROM OPENQUERY(ENVSVRCMMSBI001, N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = ''SLV'', @comm_group1 = ''%'', @comm_group2 = ''%'', @productcode = ''%'', @productfamily = ''%'' WITH RESULT SETS ( { column_specification} ); '); --------------------------------------------------------------------------- error showing is OLE DB provider "SQLNCLI11" for linked server "ENVSVRCMMSBI001" returned message "Syntax error, permission violation, or other nonspecific error". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = 'SLV', @comm_group1 in ('%'), @comm_group2 in ('%'), @productcode in ('%'), @productfamily in ('%') WITH RESULT SETS ( { column_specification} ); " for execution against OLE DB provider "SQLNCLI11" for linked server "ENVSVRCMMSBI001". ------------------------------------------------------------------------------------------------------------ i run another query like this select * from openquery(ENVSVRCMMSBI001,'USP_ENV_Value_Per_Part ''SLV'' ,''%'',''%'' ,''%'',''%''') error showing is: Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because every code path results in an error; see previous errors for some of these. Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 1 Could not find stored procedure 'USP_ENV_Value_Per_Part'.
I'm confused. Those error messages are not about this stored procedure.
those errors are related to the below query SELECT A.* FROM OPENROWSET('SQLNCLI11','Server=.;Trusted_Connection=yes;','exec USP_ENV_Value_Per_Part ''SLV'' ,''%'',''%'' ,''%'',''%''') AS A; why error is coming stored procedure is running fine when executing like USP_ENV_Value_Per_Part 'SLV' ,'%','%' ,'%','%'
hey anybody solve this issue. how to execute parameterized stored procedure using openrowset or openquery give the correct syntax for the query SELECT * into #tempt FROM OPENQUERY(ENVSVRCMMSBI001, N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = ''SLV'', @comm_group1 in (''%''), @comm_group2 in (''%''), @productcode in (''%''), @productfamily in (''%'') WITH RESULT SETS ( { column_specification} ); '); which is showing error OLE DB provider "SQLNCLI11" for linked server "ENVSVRCMMSBI001" returned message "Syntax error, permission violation, or other nonspecific error". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = N'SLV', @comm_group1 = N'%', @comm_group2 = N'%', @productcode = N'%', @productfamily = N'%' WITH RESULT SETS ( { column_specification} ); " for execution against OLE DB provider "SQLNCLI11" for linked server "ENVSVRCMMSBI001".
You are posting error messages that talk about a different stored procedure so I don't know where to start. I suggest you test this step by step because you have a lot of things going on here. Start with the simplest possible thing. Make one change. Test. Repeat until it fails. Then you'll know where the real problem lies. Then you might get more help here. These are the kind of steps I would try: Run the proc on the server where it's located. Load the temp table from the proc on that server. Run the proc via the linked server. Load the temp table from the proc via the linked server.
SELECT [site],[PART_NO],sum([QUANTITY]) FROM OPENQUERY(ENVSVRCMMSBI001, N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = ''SLV'', @comm_group1 = ''%'', @comm_group2 = ''%'', @productcode = ''%'', @productfamily = ''%'' ') where part_no='14-020.08.04.0054' group by [site],[PART_NO] --this is working fine and giving result also
SELECT [site],[PART_NO],sum([QUANTITY]) FROM OPENQUERY(ENVSVRCMMSBI001, N'EXEC [CMMS_ENB_REPORTS].[dbo].[USP_ENV_Value_Per_Part] @site = ''SLV'', @comm_group1 = ''%'', @comm_group2 = ''%'', @productcode = ''%'', @productfamily = ''%'' ') where part_no='14-020.08.04.0054' group by [site],[PART_NO]
18 People are following this question.
