CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Warric Ritchie
Warric Ritchie

Reputation: 141

Column values as stored procedure input

I have the following code when inputting parameters for a SP:

USE [MDAmanager]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[spCougarExport]
        @PropertyCodeString = NULL,
        @Date = NULL,
        @InferDate = NULL,
        @TransactionCodeString = NULL

SELECT  'Return Value' = @return_value

GO

For the @PropertyCodeString I would like to pass the values from a seperate table with all the property codes.

Below is an example of the property codes

enter image description here

What I'm trying to achieve is have the SP always return all properties and not require updating the property codes when a new property is added.

Any assistance or direction would be greatly appreciated.

Upvotes: 0

Views: 102

Answers (1)

RT72
RT72

Reputation: 191

Assuming you are able to amend your SP then you can utilise a user defined table type e.g.

CREATE TYPE [dbo].[PropertyCode_UDT] AS TABLE
(   [PropertyCode] [bigint] NOT NULL
)
GO

DECLARE @PCTab [dbo].[PropertyCode_UDT]
;

INSERT INTO @PCTab
(   [PropertyCode]
)
SELECT PC.[PropertyCode]
FROM [dbo].[PropertyCode] PC
;

DECLARE @return_value int

EXEC    @return_value = [dbo].[spCougarExport]
    @PropertyCode  = @PCTab,
    @Date = NULL,
    @InferDate = NULL,
    @TransactionCodeString = NULL
;

Your Sp will need to be amended to accept the correct type for @PropertyCode and you can just join your query within the SP on this table to get all the Property Codes

Eg

CREATE PROCEDURE [dbo].[spCougarExport]
    @Date [date]
,   @InferDate [date]
,   @TransactionCodeString [nvarchar] (MAX)
,   @PropertyCode PropertyCode_UDT READONLY
AS BEGIN;
.......
SELECT ...
FROM @PropertyCode atPC

Upvotes: 1

Related Questions