Running SP with paramters - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Running SP with paramters

Author

Topic

olepadre
Starting Member

9 Posts

Posted - 2008-12-09 : 10:20:08

I need to run up_InsertCustomers with the following parameters, selecting info from a table


declare @name as varchar(50)
declare @address1 as varchar(50)
declare @address2 as varchar(50)
declare @city as varchar(30)
declare @state as varchar(5)
declare @zipcode as varchar(15)
declare @country as varchar(30)
declare @accountchampionID as int

exec dbo.up_InsertCustomer(????)
SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7,Col8 FROM Sheet$1

How and where do I place the variable so they can be "filled" from the select Statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 10:23:50

[code]
declare @name as varchar(50)
declare @address1 as varchar(50)
declare @address2 as varchar(50)
declare @city as varchar(30)
declare @state as varchar(5)
declare @zipcode as varchar(15)
declare @country as varchar(30)
declare @accountchampionID as int

SELECT @name=Col1, @address1=Col2, @address2 =Col3, @city=Col4,@state= Col5, @zipcode=Col6, @country=Col7,@accountchampionID=Col8
FROM Sheet$1

exec dbo.up_InsertCustomer @name, @address1, @address2, @city,@state, @zipcode, @country,@accountchampionID[/code]

for this to work as expected you should have only a single record returned by select query

olepadre
Starting Member

9 Posts

Posted - 2008-12-09 : 11:10:55

is there a way to "loop" the procedure the select statement will return over 80 records. I need to try and do it this way because the SP_InsertCustomer passes the values to a second SP?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:15:47

quote:
Originally posted by olepadre

is there a way to "loop" the procedure the select statement will return over 80 records. I need to try and do it this way because the SP_InsertCustomer passes the values to a second SP?

then why dont you make second procedure to UDF so that you can just use


SELECT dbo.up_InsertCustomer(Col1,Col2,Col3,Col4,Col5, Col6,  Col7,Col8 )
FROM Sheet$1

olepadre
Starting Member

9 Posts

Posted - 2008-12-09 : 11:20:53

I'm a newbie with SQL, UDF??

Padre--

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:22:55

http://www.sqlteam.com/article/user-defined-functions

olepadre
Starting Member

9 Posts

Posted - 2008-12-09 : 11:39:52

Thanks, but functions are little over my head right now. Any chance I can send you the code for the 2 SP and see if you can help me out?

Padre--

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:51:10

post it here. will try to sort it out

olepadre
Starting Member

9 Posts

Posted - 2008-12-09 : 11:59:15

this is the "modify" view of the Stored procedures.

I basically need to run up_InsertCustomer getting the values from sheet1$ (a temp table) I didn't think I could use "copy" or import data because of the two stored procedures tied together. and the IF CLAUSE in the first needs to be checked.


ALTER PROCEDURE [dbo].[up_InsertCustomer]
(
	@name varchar(50),
	@address1 varchar(50),
	@address2 varchar(50),
	@city varchar(30),
	@state varchar(5),
	@zipcode varchar(15),
	@country varchar(30),
	@accountChampionID int
)
AS

SET NOCOUNT ON

/* declare variables */
DECLARE @customerID int

/* set the customer id variable */
SET @customerID = (SELECT ID FROM dbo.Customers WHERE Name = @name)

/* 
	do we already have a customer name that matches
	the @name variable
*/
IF @customerID IS NULL 
	BEGIN
		/* add a new record to the dbo.Customers table */
		INSERT INTO dbo.Customers (Name)
		VALUES(@name)

		/* 
			after the record has been created
			get the new id and pass it to the
			up_InsertCustomerLocation procedure
		*/
		SET @customerID = (SELECT TOP 1 ID FROM dbo.Customers ORDER BY ID DESC)
	END

EXEC dbo.up_InsertCustomerLocation @customerID, @address1, @address2, @city,
	@state, @zipcode, @country, @accountChampionID

Here is the second SP


ALTER PROCEDURE [dbo].[up_InsertCustomerLocation]
(
	@customerID int,
	@address1 varchar(50),
	@address2 varchar(50),
	@city varchar(30),
	@state varchar(5),
	@zipcode varchar(15),
	@country varchar(30),
	@accountChampionID int
)
AS

SET NOCOUNT ON

/* declare variables */
DECLARE @locationID int

/* set the location id variable */
SET @locationID = (SELECT ID FROM dbo.CustomerLocations WHERE CustomerID = @customerID AND Address1 = @address1 AND City = @city)

/* 
	do we already have a location that matches
	the @address1 and @city variables
*/
IF @locationID IS NULL 
	BEGIN
		INSERT INTO dbo.CustomerLocations 
		(
			CustomerID, 
			Address1, 
			Address2, 
			City, 
			State, 
			Zipcode, 
			Country,
			AccountChampionID
		)
		VALUES
		(
			@customerID,
			@address1,
			@address2,
			@city,
			@state,
			@zipcode,
			@country,
			@accountChampionID
		)
	END

Padre--

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 12:10:19

what you want is just this


ALTER PROCEDURE [dbo].[up_InsertCustomer]
(
	@name varchar(50),
	@address1 varchar(50),
	@address2 varchar(50),
	@city varchar(30),
	@state varchar(5),
	@zipcode varchar(15),
	@country varchar(30),
	@accountChampionID int
)
AS

SET NOCOUNT ON

/* declare variables */
DECLARE @customerID int

/* set the customer id variable */
SET @customerID = (SELECT ID FROM dbo.Customers WHERE Name = @name)

/* 
	do we already have a customer name that matches
	the @name variable
*/
IF @customerID IS NULL 
	BEGIN
		/* add a new record to the dbo.Customers table */
		INSERT INTO dbo.Customers (Name)
		VALUES(@name)

		/* 
			after the record has been created
			get the new id and pass it to the
			up_InsertCustomerLocation procedure
		*/
		SET @customerID = (SELECT TOP 1 ID FROM dbo.Customers ORDER BY ID DESC)
	END

/* declare variables */
DECLARE @locationID int

/* set the location id variable */
SET @locationID = (SELECT ID FROM dbo.CustomerLocations WHERE CustomerID = @customerID AND Address1 = @address1 AND City = @city)

/* 
	do we already have a location that matches
	the @address1 and @city variables
*/
IF @locationID IS NULL 
	BEGIN
		INSERT INTO dbo.CustomerLocations 
		(
			CustomerID, 
			Address1, 
			Address2, 
			City, 
			State, 
			Zipcode, 
			Country,
			AccountChampionID
		)
		VALUES
		(
			@customerID,
			@address1,
			@address2,
			@city,
			@state,
			@zipcode,
			@country,
			@accountChampionID
		)
	END
GO

also if CustomerID is identity column use SCOPE_IDENTITY to return last inserted id instead of max(id).

olepadre
Starting Member

9 Posts

Posted - 2008-12-09 : 12:34:29

OK, this is combining the two into one. I got that, but how do I "read" the values from dbo.sheet1$ and insert them into the variables?

Padre--

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 12:37:55

quote:
Originally posted by olepadre

OK, this is combining the two into one. I got that, but how do I "read" the values from dbo.sheet1$ and insert them into the variables?

Padre--

is sheet1 excel sheet? if yes, use OPENROWSET.

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/import-export-to-excel.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources