Joining SQL Server Stored Procedure to SQL Server Table

[JimP]

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?

 

[Tim Zych]

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 structure:

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

 

[JimP]

All good ideas - thank you.

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 structure:

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