Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 40
  • Last Modified:

How would you convert the following SQL Statement into a stored procedure using passed in parameters with SQL Server 2008?

How would you convert the following SQL Statement into a stored procedure in SQL Server 2008?

The fields starting with "str" would be passed in as parameters:



INSERT into dbo.tbl_CSL_ImportMonthlyChecks (bankname, amt1, banknumber, amt2, datefile, dateimported, importedby)
VALUES ((SELECT bankDescr from dbo.tbl_CSL_Bank WHERE bankID = strBank), strAmt1, strBank, strAmt2, strFiledate, strImportdate, strImportby);
0
zimmer9
Asked:
zimmer9
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Here it is

CREATE PROC ProcName
AS
BEGIN

	SET NOCOUNT ON;

	INSERT INTO dbo.tbl_CSL_ImportMonthlyChecks (bankname, amt1, banknumber, amt2, datefile, dateimported, importedby)
	VALUES ((SELECT bankDescr from dbo.tbl_CSL_Bank WHERE bankID = strBank), strAmt1, strBank, strAmt2, strFiledate, strImportdate, strImportby)

END

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
This should give you a start.  Couple of things...
  • Note the @columnname use of parameters in the CREATE PROC statement, then in the SELECT clause.
  • In an INSERT statement if any of the values inserted come from a table then use SELECT instead of VALUES.
  • Are all of these parameters string values, or is Amt numeric and the dates dates?  I assumed the latter, and changed the prefixes to match.  If they are all strings then you'll want to change back to strings plus do a CAST(column_name as varchar(?)) in the SELECT.
  • The columns in the INSERT didn't seem to match the columns in the SELECT, so eyeball these and make sure they line up. Especially bankID = strBank as ID implies numeric and str implies string.

CREATE PROC YourProc(@strBank varchar(100), @Amt numeric(9,2), @dtFileDate date, @dtImportDate date, @strImportBy varchar(100)) AS 

/*
Meaningful code comments go here
zimmer9  2016-10-08  With a little help from https://www.experts-exchange.com/questions/28975167/How-would-you-convert-the-following-SQL-Statement-into-a-stored-procedure-using-passed-in-parameters-with-SQL-Server-2008.html
*/

INSERT INTO dbo.tbl_CSL_ImportMonthlyChecks (bankname, amt1, banknumber, amt2, datefile, dateimported, importedby)
SELECT bankDescr, @Amt, BankNumber, @Amt, @dtFileDate, @dtImportDate, @strImportBy
FROM dbo.tbl_CSL_Bank 
WHERE bankID = @strBank
GO

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now