如何将查询放在存储过程中 [英] how to put the query in a stored procedure

问题描述

USE RAUSDXT02



- drop table ## tbl_STORE_LIST



select store_code INTO ## tbl_STORE_LIST从商店



其中



store_code不在（'722'，'803'，'8000' ）和



date_closed是NULL和



global_tax_code不是NULL和



store_type ='S'



- 选择*来自## tbl_STORE_LIST



DECLARE @store_code nvarchar（20）





DECLARE store_code_cursor CURSOR FOR SELECT DISTINCT store_code FROM ## tbl_STORE_LIST

OPEN store_code_cursor

FETCH NEXT FROM store_code_cursor INTO @store_code





WHILE @@ FETCH_STATUS = 0

BEGIN

EXEC XP_CmdShell'BCP从## tbl_STORE_LISTqueryout\\dalsqlposdev2 \ RADATA \Debopam \TRAFFIC \ ACTIVESTORES中选择*。 txt-c -T



FETCH NEXT FROM store_code_cursor INTO @store_code

END

CLOSE store_code_cursor

DEALLOCATE store_code_cursor



DROP TABLE ## tbl_STORE_LIST





我想把这个上面的脚本放在一个SP中，这样我就可以了只需使用EXEC SP_name执行查询

解决方案

 创建 < span class =code-keyword> procedure  SP_ProcedureName 
   -   （@ storecode varchar（ 50））//如果你想把它作为参数传递，你需要将它作为逗号分隔值字符串格式传递 
   -   < span class =code-comment>您需要将CSV格式转换为表格格式 
   
 开始 
  USE  RAUSDXT02 
 
   -    drop table ## tbl_STORE_LIST  
 
 选择 store_code  INTO  ## tbl_STORE_LIST 从商店
 
 其中 
 
 store_code 不  （'  722'，'  803'，'  8000'）< span class =code-keyword>和 
   -    store_code not in（@storecode ）和 
 
 date_closed    NULL  和 
 
 global_tax_code   不  NULL  和 
 
 store_type = '  S' 
 
   -    select * from ## tbl_STORE_LIST  
 
  DECLARE   @ store_code   nvarchar （ 20 ）
 
 
  DECLARE  store_code_cursor  CURSOR   FOR   SELECT   DISTINCT  store_code  FROM  ## tbl_STORE_LIST 
  OPEN  store_code_cursor 
  FETCH  NEXT  FROM  store_code_cursor  INTO   @ store_code  
 
 
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  
  EXEC  XP_CmdShell '  BCPselect * from ## tbl_STORE_LISTqueryout\\ \\\dalsqlposdev2 \ RADATA \Debopam \TRAFFIC \ACTIVESTORES.txt-c -T' 
 
  FETCH  NEXT  FROM  store_code_cursor  INTO   @ store_code  
  END  
  CLOSE  store_code_cursor 
  DEALLOCATE  store_code_cursor 
 
  DROP   TABLE  ## tbl_STORE_LIST 
 端 

USE RAUSDXT02

--drop table ##tbl_STORE_LIST

select store_code INTO ##tbl_STORE_LIST from store

where

store_code not in ('722','803','8000') and

date_closed is NULL and

global_tax_code is not NULL and

store_type='S'

--select * from ##tbl_STORE_LIST

DECLARE @store_code nvarchar(20)


DECLARE store_code_cursor CURSOR FOR SELECT DISTINCT store_code FROM ##tbl_STORE_LIST
OPEN store_code_cursor
FETCH NEXT FROM store_code_cursor INTO @store_code


WHILE @@FETCH_STATUS = 0
BEGIN
EXEC XP_CmdShell 'BCP "Select * from ##tbl_STORE_LIST" queryout "\\dalsqlposdev2\RADATA\Debopam\TRAFFIC\ACTIVESTORES.txt" -c -T'

FETCH NEXT FROM store_code_cursor INTO @store_code
END
CLOSE store_code_cursor
DEALLOCATE store_code_cursor

DROP TABLE ##tbl_STORE_LIST


I want to put this above script in an SP so that I can simply use EXEC SP_name to execute the query

解决方案

create procedure SP_ProcedureName
--(@storecode varchar(50)) //if you want to pass this as parameter, you need to pass it as comma seperated value string format
  --and you need to convert the   CSV to a table format
as
begin
USE RAUSDXT02
 
--drop table ##tbl_STORE_LIST
 
select store_code INTO ##tbl_STORE_LIST from store
 
where 
 
 store_code not in ('722','803','8000') and 
--store_code not in (@storecode) and 

date_closed is NULL and 

global_tax_code is not NULL and

store_type='S'

--select * from ##tbl_STORE_LIST
 
DECLARE @store_code nvarchar(20)
 

DECLARE store_code_cursor CURSOR FOR SELECT DISTINCT store_code FROM ##tbl_STORE_LIST
OPEN store_code_cursor
FETCH NEXT FROM store_code_cursor INTO @store_code
 

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC XP_CmdShell 'BCP "Select * from ##tbl_STORE_LIST" queryout "\\dalsqlposdev2\RADATA\Debopam\TRAFFIC\ACTIVESTORES.txt" -c -T'
 
FETCH NEXT FROM store_code_cursor INTO @store_code
END
CLOSE store_code_cursor
DEALLOCATE store_code_cursor
 
DROP TABLE ##tbl_STORE_LIST
end

这篇关于如何将查询放在存储过程中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！