Joining SQL Server Stored Procedure to SQL Server Table
 

Is this possible? e.g.

This code executes a stored procedure with one argument for "Customer Type"
EXECUTE dbo.mysp.spname @cust_type = 'A'

1. How can I store the value for Customer Type in a cell to update the sp at
runtime?

2. How can I link the sp to another SQL Server table (e.g. to get Customer
name?) e.g.
SELECT n.cust_name FROM dbo.cust_names as n
JOIN (the stored procedure as s) ON s.cust_id = n.cust_id

3. How do I execute this?

Joining SQL Server Stored Procedure to SQL Server Table
 

Range("A1").Value = "A"
' Worksheets("Sheet1").Range("A1").Value = "A"
' Sheet1.Range("A1").Value = "A"
' ThisWorkbook.Names("SomeNamedCell").RefersToRange. Value = "A"

Dim cmdText as String
cmdText = "EXECUTE dbo.mysp.spname @cust_type = '" & Range("A1").Value & "'"

Don't forget to double the single quotes if @cust_type is a varchar and
might
have single quotes in it, so revise the last statement:

cmdText = "EXECUTE dbo.mysp.spname @cust_type = '" &
Replace(Range("A1"),"'","''").Value & "'"

AFAIK you cannot join to a stored proc as you prototyped. But you can use a
user defined table function which accepts parameters and can be used inline
as a regular table.
Here is an example.
http://www.sqlteam.com/article/user-defined-functions

But do you need to do it that way? Another way is to modify your SQL a
bit...imagining your table structu

CREATE PROCEDURE spname
(
@cust_type VARCHAR(10)
)
AS
SELECT
n.cust_name
FROM dbo.cust_names n
INNER JOIN dbo.cust c
ON n.cust_id = c.cust_id
WHERE c.cust_type = @cust_type


--
Tim Zych
http://www.higherdata.com
Workbook Compare - free and pro versions


"JimP" wrote in message
...
Is this possible? e.g.

This code executes a stored procedure with one argument for "Customer
Type"
EXECUTE dbo.mysp.spname @cust_type = 'A'

1. How can I store the value for Customer Type in a cell to update the sp
at runtime?

2. How can I link the sp to another SQL Server table (e.g. to get Customer
name?) e.g.
SELECT n.cust_name FROM dbo.cust_names as n
JOIN (the stored procedure as s) ON s.cust_id = n.cust_id

3. How do I execute this?

Joining SQL Server Stored Procedure to SQL Server Table
 

All good ideas - thank you.


"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...

Range("A1").Value = "A"
' Worksheets("Sheet1").Range("A1").Value = "A"
' Sheet1.Range("A1").Value = "A"
' ThisWorkbook.Names("SomeNamedCell").RefersToRange. Value = "A"

Dim cmdText as String
cmdText = "EXECUTE dbo.mysp.spname @cust_type = '" & Range("A1").Value &
"'"

Don't forget to double the single quotes if @cust_type is a varchar and
might
have single quotes in it, so revise the last statement:

cmdText = "EXECUTE dbo.mysp.spname @cust_type = '" &
Replace(Range("A1"),"'","''").Value & "'"

AFAIK you cannot join to a stored proc as you prototyped. But you can use
a user defined table function which accepts parameters and can be used
inline as a regular table.
Here is an example.
http://www.sqlteam.com/article/user-defined-functions

But do you need to do it that way? Another way is to modify your SQL a
bit...imagining your table structu

CREATE PROCEDURE spname
(
@cust_type VARCHAR(10)
)
AS
SELECT
n.cust_name
FROM dbo.cust_names n
INNER JOIN dbo.cust c
ON n.cust_id = c.cust_id
WHERE c.cust_type = @cust_type


--
Tim Zych
http://www.higherdata.com
Workbook Compare - free and pro versions


"JimP" wrote in message
...
Is this possible? e.g.

This code executes a stored procedure with one argument for "Customer
Type"
EXECUTE dbo.mysp.spname @cust_type = 'A'

1. How can I store the value for Customer Type in a cell to update the sp
at runtime?

2. How can I link the sp to another SQL Server table (e.g. to get
Customer
name?) e.g.
SELECT n.cust_name FROM dbo.cust_names as n
JOIN (the stored procedure as s) ON s.cust_id = n.cust_id

3. How do I execute this?