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 justcreate procedure your_stored_procedureasbegin <your query here>end you might also want to pass in the value via input parameters KH |
|
|
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 examplesquote: CREATE PROCEDURECreates 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.SyntaxCREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] ExamplesA. Use a simple procedure with a complex SELECTThis 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 pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT 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_idGOThe au_info_all stored procedure can be executed in these ways:EXECUTE au_info_all-- OrEXEC au_info_allOr, if this procedure is the first statement within the batch:au_info_allB. 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 pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P') DROP PROCEDURE au_infoGOUSE pubsGOCREATE 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 = @lastnameGO
KH |
|
|
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 byinput parameter.My main problem is For this i do not want to use 26 input parameter.Ranjeet Kumar Singh |
|
|
ranjeetsingh_6
Posting Yak Master
125 Posts |
Posted - 2007-01-27 : 01:26:02
|
I want somethings like thiscreate procedure insertInto_tbltgr@InsertedValue varchar(1000)asdeclare @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',2exec insertInto_tbltgr @valueRanjeet Kumar Singh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
ranjeetsingh_6
Posting Yak Master
125 Posts |
Posted - 2007-01-27 : 01:37:42
|
yes i want use dynamic queryRanjeet Kumar Singh |
|
|
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 |
|
|
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, ...ASBEGIN 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 isKristen |
|
|
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 |
|
|
|
|
|