Stored procedure needs to be created

Transact-SQL
1

Hi All,
Though i work in the oracle platform but recently got one project where we need to write a SP in the sql server, the requirement is like as below:

  1. SP should accept 2 parameters one is event id which is having number datatype and 2nd parameter is a combination of few of the columns such as question no./name/description,ques_dtl,ques_ans which will be comma separated.

  2. The 2nd parameter can contain multiple values which will be separated by #, i have to read these values and get them inserted into some tables.

could you please help me to write some generic SP which can fulfill my above mentioned requirement, I shall be really much thankful for the same.

2

Welcome

The above statement is contradictory. 1st one says comma delimited the second says # delimited

What is the delimiter for the 2nd parameter ?

3

Hi, Thanks for the response.

here it the input values which we will get in the 2nd parameter:

1,QuestT,QuestText,QuestDtl,QuestANS#2,QuestT,QuestText,QuestDtl,QuestANS#5,QuestT,QuestText,QuestDtl,QuestANS

each record will have 5 values seprated by comma and we may get multiple records as input and they all will be separated by #

hope i am able to answer your question.

4

You might want to rethink this design by implementing user defined table types

Create type questionType as table(
QuestNumber int,
QuestionName nvarchar(150),
)

Etc listing out all of the parameters.

Then in the sp you can use it as follows

Create procedure questions_sp
(@eventid int,
@tvp questionType readonly
)
as
Begin
End

Otherwise you will need to do double parsing one for comma the other for #

Can you change your design or it is baked

5

This design is almost baked and database architects have finalized this approach as well, something if you can give me like inserting the 1 record as below:

1,QuestT,QuestText,QuestDtl,QuestANS

how i will identify the columns here based on comma separation and this record needs to be coming in a single input parameter.

6

This has nothing to do with architecturing databases. How about sending this as json. The approache recommended by "database architects" is not well thought out

7

yes that should work but i am not sure how i will read the json format and get it inserted into the database.

8

We will guide you. Give me a minute.

Json would be

[{"eventId": 1, "QuestT": "13"}] etc.

Create proc questions_sp
As
(
@parms varchar(max)
)
Begin
Create table #questions(
eventId int,
QuestT nvarchar(150),
QuestText nvarchar(150),
QuestDtl nvarchar(150),
QuestANS nvarchar(150)
)

Insert into #questions
Select *
From openjson(@questions)
With (
EventId int,
QuestT nvarchar(150),
QuestText nvarchar(150),
QuestDtl nvarchar(150),
QuestANS nvarchsr(150)
)

--then you can do what you need 
--with this temp table to manage to destination table
End
Go
1 Like
9

But remember this will not scale since an navarchar param has limits whereas

Create type questionType as table(
QuestNumber int,
QuestionName nvarchar(150),
)

Is limitless, something you might to share with your "database architects"

10

yes correct but how declaring table type variable will read the multiple records passed in the input and distinguish the value of the respective column coming as comma separated.

11

As the name indicates it is a table type no need for commas. The parameter itself is a table. Read more about it in documentation

12

for sure i will go through it but how the data will be inserted into this as the user will only insert data via the stored procedure only?
and when the data will be like as below:

1,QuestT,QuestText,QuestDtl,QuestANS#2,QuestT,QuestText,QuestDtl,QuestANS#5,QuestT,QuestText,QuestDtl,QuestANS

13

here is one ugly way, that will come to bite you one day
NOT RECOMMENDED


declare @questions nvarchar(max) = 
'1,35min,How old are you?,age question,21#2,12min,what is your income level,income,35k#5,1min,where do you live?,location,Kabul'

create table #questions(
eventid int, 
QuestT nvarchar(150), 
QuestText nvarchar(150),
QuestDtl nvarchar(150),
QuestANS nvarchar(150)
)

;WITH pounds
AS
     
(
	select value
	from string_split(@questions, '#') c
)
insert into #questions  
select [1] eventId,
       [2] as QuestT,
	   [3] as QuestText,
	   [4] QuestDtl,
	   [5] as QuestANS
  from (
  select p.value as ID, c.value, ROW_NUMBER() OVER(PARTITION BY p.value ORDER BY (SELECT NULL)) as rn
   FROM pounds p
   cross apply string_split(p.value, ',') c
  ) x
 PIVOT( 
      MAX(value) FOR rn IN([1],[2],[3],[4],[5]) 
	  ) as PVT

select * From #questions

drop table #questions
14

unfortunate thing here is we do not have create table/table type permission and we are not going to get it also :frowning:

15

.That was just a sample temp table for you to test the approach of comma delimited. You can use a table variable

16

Even table type permission i am not going to have, below is what i tried but failed

CREATE TYPE RsvpTableType
AS TABLE
( QSTN_NBR INT
, QSTN_DESC VARCHAR(255)
, ACTV_QSTN_IND BIT
, MNDTR_QSTN_IND BIT
, ANS_NBR BIT
, ANS_DESC VARCHAR(100)
, ACTV_ANS_IND BIT);
GO
CREATE PROCEDURE dbo.upsert_evnt_rsvp_dtl1
@Rs RsvpTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO dbo.EVNT_RSVP
(
evnt_id
, QSTN_NBR
, ACTV_QSTN_IND
, CRTE_USR_ID
, CRTE_TSTP
)
SELECT QSTN_NBR, QSTN_NBR,ACTV_QSTN_IND,'Test', GETDATE()
FROM @Rs;
GO

Msg 262, Level 14, State 1, Line 20
CREATE TYPE permission denied in database 'SMR_EVT'.

17

What environment is this in? And are you a dba

18

no i am not a DBA and this is developement env.

19

Then just go with the comma approach and replace insert into #questions with

INSERT INTO dbo.EVNT_RSVP
(
evnt_id
, QSTN_NBR
, ACTV_QSTN_IND
, CRTE_USR_ID
, CRTE_TSTP
)