MavsNation MavsNation - 4 months ago 20
SQL Question

SQL. Trying to create a stored procedure on SQL, but not sure if I have the right query

Create a SQL stored procedure that parses a string into previously unknown number of fields. The inputs would be


  1. a text string of undetermined length;

  2. a delimiter, passed as a string;

  3. a nullable column that, if relevant, would pass the text indicator as a single character string.



The resulting table would be entirely dependent on the string used as an input

Please help because I cannot figure this out. I know this is wrong, but I had no clue where to begin.

Here is what I have tried so far:

DECLARE @l INT, @c VARCHAR(MAX) = ''
SELECT @l = MAX(LEN(n)) FROM AdventureWOrk
DECLARE @s NVARCHAR(MAX) = '

;WITH cte AS


)

Insert INTO @Values (1, 'CGID', 'EENumber', 'EEYID', 'SSN' )
SELECT
[Value],
[Value],
Prod_Attributes.value('/Attribute[1]','varchar(MAX)') AS [CGID],
Prod_Attributes.value('/Attribute[2]','varchar(MAX)') AS [EENUMBER],
Prod_Attributes.value('/Attribute[3]','varchar(MAX') AS [EYEID],
Prod_Attributes.value('/Attribute[4]','varchar(MAX') AS [SSN]

Answer

You can create a stored procedure by using the following syntax:

CREATE PROCEDURE usp_YourProcedure
AS 
BEGIN
  -- Your logic
END

You would put the code you already have within the BEGIN statement.

To execute the stored procedure you can do:

EXEC usp_YourProcedure

To add parameters, simply state them after the CREATE PROCEDURE declaration.

CREATE PROCEDURE usp_YourProcedure
(
    @TextField  VARCHAR(MAX), 
    @Delimeter  VARCHAR(1),
    @TextIndicator CHAR(1) = NULL
)
AS
BEGIN

END

Then to execute with parameters:

EXEC usp_YourProcedure  'String literal, with commas, which will, be stripped out by the delimiter', ','

Further details are outlined at MSDN.

As an additional note, try keeping your variable names descriptive and consistent, also check the casing.