存储采购方法 [英] stored procure methods

问题描述

当前我正在使用sqlserver 2005
我有一个查询，希望在存储过程中使用..

谁能指导我如何在存储过程中转换以下查询

Currently i am working on sqlserver 2005
i have a query and wanted that to use in stored procedure..

Can anyone guide me about how to convert the following query in stored procedure

"select CId,SrNo,Date,Name,Contact,Contact1,ResNumber,DOB,Age,Location,Qualification,CurrentOrganizatn,WorkExp,CalledBy,EQId,SentTo,HRStatus,Process,SentTo1,HRStatus1,Process1,SentTo2,HRStatus2,Process2,SentTo3,HRStatus3,Process3,SentTo4,HRStatus4,Process4,Remark,Remark1 from TotTab where convert(datetime,Date,105) between convert(datetime,''" + DispatchFrom.Text + "'',105) and convert(datetime,''" + DispatchTo.Text + "'',105) and HRStatus like ''rej%'' or convert(datetime,Date,105) between convert(datetime,''" + DispatchFrom.Text + "'',105) and convert(datetime,''" + DispatchTo.Text + "'',105) and HRStatus like ''onhold%''" 

我是新手，所以

i am new to it so

推荐答案

这是用于创建存储过程的sql

Here is sql to create stored procedure

Create procedure My_Procedure @DispatchFrom datetime,@DispatchTo datetime
AS
select CId,SrNo,Date,Name,Contact,Contact1,ResNumber,DOB,Age,Location,Qualification,CurrentOrganizatn,WorkExp,CalledBy,EQId,SentTo,HRStatus,Process,SentTo1,HRStatus1,Process1,SentTo2,HRStatus2,Process2,SentTo3,HRStatus3,Process3,SentTo4,HRStatus4,Process4,Remark,Remark1 from TotTab where convert(datetime,Date,105) between @DispatchFrom and @DispatchTo and HRStatus like 'rej%' or convert(datetime,Date,105) between @DispatchFrom  and @DispatchTo and HRStatus like 'onhold%'

要执行sp(例如):

To execute sp (for example) :

exec My_Procedure '2011-12-01','2011-12-31'

<pre>SELECT
name,
Designation,
Deptt,
Location,
emailid,
officeno,
personalcontactno,
address
from empbirth where userid="+txtuserid.Text+"</pre>

此查询的SP

SP for this query

USE [Omxintranet_DEV]
GO

/****** Object:  StoredProcedure [dbo].[select_Emp]    Script Date: 12/16/2011 17:40:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[select_Emp]
@userid varchar(20)	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT
name,
Designation,
Deptt,
Location,
emailid,
officeno,
personalcontactno,
address
from empbirth where userid=@userid

END

GO

在您的查询中使用的示例与上面的示例相同.

in your query uses same as above example.

此存储过程正常运行，请使用它.

This storeprocedure is working correctly,use it.

CREATE PROCEDURE  ProcedureName
@Name varchar(50),
@Password varchar(50)	
AS
BEGIN

insert into TableName(Name,Password)values(@Name,@Password)	 
 
END 
GO

这篇关于存储采购方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！