October 20, 2010 at 3:10 am
Is there of way of declaring variable that take one or more than one values based on the users's need in a store procedure
I have this small code below which is not working but, just trying to give more clue of what I want.
Declare @Account_N0 varchar (20)
SET @Account_N0 in ('031-530-9','031-530-10','031-530-2')
October 20, 2010 at 3:32 am
What I understood you need conceptually is a dynamic array of varchar(20) as a input parameter of a stored procedure. You can achieve this in SQL Server 2008 using the new Table Type feature.
Steps
-- Create a User Defined Table Type
-- Declare Input parameter of the Procedure as TableType
Following code sample will give you some idea about the implementation
-- Creating Table type
CREATE TYPE [dbo].[TestType] AS TABLE(
[col1] [varchar](20) NULL
)
-- Use the TableType as Input Parameter of Procedure
Create procedure [dbo].[TestProc]
(
@InpTable TestType READONLY
)
You can use the input parameter @InpTable similar to a Table Variable for data fetching.. Only limitation is any DML operation on the Input parameter ( @InpTable) within the procedure is restricted. Calling Application / Procedure should populate the table @InpTable
October 20, 2010 at 4:07 am
Thanks for the respond. This is too much for me to understand. I still new in SQL.
When I try to create the table it complained but did managed to fix it. Now is the store proc
is complains about something near READONLY
see below
"Msg 102, Level 15, State 1, Procedure TestProc, Line 3
Incorrect syntax near 'READONLY'."
/*
What I understood you need conceptually is a dynamic array of varchar(20) as a input parameter of a stored procedure. You can achieve this in SQL Server 2008 using the new Table Type feature.
Steps
-- Create a User Defined Table Type
-- Declare Input parameter of the Procedure as TableType
Following code sample will give you some idea about the implementation
*/
-- Creating Table type
CREATE table [dbo].#TestType (
[col1] [varchar](20) NULL
)
-- Use the TableType as Input Parameter of Procedure
Create procedure [dbo].[TestProc]
(
@InpTable #TestType READONLY
)
October 20, 2010 at 11:05 am
There are several ways to accomplish what you need.
One is a table variable input.
Another is to parse a string into a table.
Another is to use the string to build "dynamic SQL".
Another would be to use an XML parameter.
Another would be to convert the date column to a string and use the Like operator to find if it matches part of the string-parameter.
Of these, I most recommend either learning how to use a table parameter, or to parse the string into a table. In either case, you can join to it.
What are you most comfortable with? Details can be given for any/all of these.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 20, 2010 at 12:27 pm
Send parameter '031-530-9,031-530-10,031-530-2'
Create a stored procedure to split values by comma
for example:
SET @Account_N0 = '031-530-9,031-530-10,031-530-2'
Select * from account where Account_No in (Select data from dbo.Split(@Account_No,',')
----- Split function code is
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
October 20, 2010 at 12:40 pm
You dont need to write a function to split string values into rows, there is a row constructor available in SQL Server 2008.
Please see below link for a sample:
http://mssqlsolutions.blogspot.com/2010/10/enhanced-values-clause-sql-server-2008.html
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
October 21, 2010 at 9:31 am
How about something like this...
Its pretty straight forward and wouldn't require any extra functions.
(However, there are perks to the function spiting apart a string)
--Holds the Account Numbers
DECLARE @account AS TABLE
(
Number VarChar(20)
)
--Insert the Account number
INSERT INTO
@account(Number)
SELECT
Number
FROM
(
VALUES
('031-530-9'),
('031-530-10'),
('031-530-2')
) AS AccountNumbers(Number)
SELECT * FROM @account
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply