SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Make Stored procedre of This Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ranjeetsingh_6
Posting Yak Master

India
125 Posts

Posted - 01/27/2007 :  01:00:05  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 01/27/2007 :  01:07:58  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 01/27/2007 :  01:09:57  Show Profile  Reply with Quote
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

India
125 Posts

Posted - 01/27/2007 :  01:14:05  Show Profile  Reply with Quote
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

India
125 Posts

Posted - 01/27/2007 :  01:26:02  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 01/27/2007 :  01:34:38  Show Profile  Reply with Quote
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

India
125 Posts

Posted - 01/27/2007 :  01:37:42  Show Profile  Reply with Quote
yes i want use dynamic query

Ranjeet Kumar Singh
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/27/2007 :  03:28:14  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Edited by - jezemine on 01/27/2007 03:28:52
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/27/2007 :  03:53:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/27/2007 :  07:49:39  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000