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);
zimmer9Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.