Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Make Stored procedre of This Query

Author  Topic 

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-01-27 : 01:00:05
Hi

I want to write Store Procedure of this given insert query Query
Plz make stored procedure of this query.

insert into tbltGR(txtgr_no,datgr_date,datgr_Entrydate,txtgr_Consignor,txtgr_consignee,txtgr_routefrom,txtgr_routeto,intgr_qty,txtgr_Materialname,intgr_invvalue,txtgr_remarks,txtgr_billbranch,txtgr_billparty,txtgr_freightmode,txtgr_bookingmode,fltgr_actualwt,fltgr_servicetax,fltgr_totalamt,txtgr_loadingBranch,txtgr_DestinstionBranch,txtgr_grtype,txtgr_privateMarka,txtgr_deliveredat,txtgr_STForm,txtgr_methodofPack,fltgr_grandtotal,txtgr_User,txtgr_branch,txtgr_pf) values('272727',convert(datetime,'27/1/2007',103),convert(datetime,'27/01/2007 10:43:21',103),'A 1 AEROROLS CANS','A 1 AEROROLS CANS','ABOHAR','QUTAB ROAD',123,'ssss',0,'Ranjeet','AMBALA','','To Pay','Road',123,2,12,'','SANJAY GANDHI TRANSPORT NAGAR','Weight Wise','sss','GODOWN','NOT ATTACHED','SSS',16,'Ranjeet','Vashi',2)

Ranjeet Kumar Singh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 01:07:58
basically it is just

create procedure your_stored_procedure
as
begin
<your query here>
end


you might also want to pass in the value via input parameters


KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 01:09:57
Books Online provide a very good description on how to do this with examples

quote:

CREATE PROCEDURE
Creates a stored procedure, which is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.

Procedures can be created for permanent use or for temporary use within a session (local temporary procedure) or for temporary use within all sessions (global temporary procedure).

Stored procedures can also be created to run automatically when Microsoft® SQL Server™ starts.

Syntax
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

Examples
A. Use a simple procedure with a complex SELECT
This stored procedure returns all authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure does not use any parameters.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO

The au_info_all stored procedure can be executed in these ways:

EXECUTE au_info_all
-- Or
EXEC au_info_all

Or, if this procedure is the first statement within the batch:

au_info_all

B. Use a simple procedure with parameters
This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO





KH

Go to Top of Page

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-01-27 : 01:14:05
Hi
inserted value in this query is not Fix this will came by
input parameter.My main problem is For this i do not want to use 26 input parameter.

Ranjeet Kumar Singh
Go to Top of Page

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-01-27 : 01:26:02
I want somethings like this

create procedure insertInto_tbltgr
@InsertedValue varchar(1000)
as
declare @columns varchar(1000)
set @columns='txtgr_no,datgr_date,datgr_Entrydate,txtgr_Consignor,txtgr_consignee,txtgr_routefrom,txtgr_routeto,intgr_qty,txtgr_Materialname,intgr_invvalue,txtgr_remarks,txtgr_billbranch,txtgr_billparty,txtgr_freightmode,txtgr_bookingmode,fltgr_actualwt,fltgr_servicetax,fltgr_totalamt,txtgr_loadingBranch,txtgr_DestinstionBranch,txtgr_grtype,txtgr_privateMarka,txtgr_deliveredat,txtgr_STForm,txtgr_methodofPack,fltgr_grandtotal,txtgr_User,txtgr_branch,txtgr_pf'
insert into tbltGR(@columns)
values(@InsertedValue)



and want to execute like this:

declare @value varchar(1000)
@value='272727',convert(datetime,'27/1/2007',103),convert(datetime,'27/01/2007 10:43:21',103),'A 1 AEROROLS CANS','A 1 AEROROLS CANS','ABOHAR','QUTAB ROAD',123,'ssss',0,'Ranjeet','AMBALA','','To Pay','Road',123,2,12,'','SANJAY GANDHI TRANSPORT NAGAR','Weight Wise','sss','GODOWN','NOT ATTACHED','SSS',16,'Ranjeet','Vashi',2

exec insertInto_tbltgr @value

Ranjeet Kumar Singh
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 01:34:38
you have to use dynamic sql exec() or sp_executesql to do this.

Take a look here http://www.sommarskog.se/dynamic_sql.html


KH

Go to Top of Page

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-01-27 : 01:37:42
yes i want use dynamic query

Ranjeet Kumar Singh
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-27 : 03:28:14
I don't think using dynamic sql is a good idea in this case. why don't you want to have 26 imput params? Just because that seems like a big number?

if you make them all default to null, you can pass as few as you like, as long as you use named params in the call, which you should always do anyway.

In any case the article that khtan pointed you to should convince you that dynamic sql is not necessary in this case.


www.elsasoft.org
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-01-27 : 03:53:03
The "best practice" way to do this is:

CREATE PROCEDURE MySProc
@txtgr_no int,
@datgr_date datetime,
...
AS
BEGIN
INSERT INTO tbltGR(txtgr_no, datgr_date, ...
VALUES(@txtgr_no, @datgr_date, ...
END

and then call it from your application preferably with a parameterized procedure call,
but failing that with a dynamic SQL call:

EXEC MySProc @txtgr_no = 272727, @datgr_date = '27/1/2007', ...

or (at greater risk for future maintenance, but assuming you can be SURE the parameter sequence
will not change AND it is for some reason difficult to get the parameter names in there):

EXEC MySProc 272727, '27/1/2007', ...

NOTE: String Dates should be in YYYYMMDD format to be unambiguous whatever the Locale of the server is

Kristen
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-27 : 07:49:39
that's what I meant, except that I was thinking the default of each sproc param would be null, so callers woudl only have to pass the params they actually wanted entered, and the rest would be entered as null. of course this requires those columns actually allow nulls.

also I wouldn't call it with unnamed params. too fragile.


www.elsasoft.org
Go to Top of Page
   

- Advertisement -