Using SELECT to provide parameter for called stored procedure

Transact-SQL
1 
DECLARE
@DOS as datetime

,@CII2T as datetime
,@CII2I as datetime
,@CID as varchar(10)
,@CDOS as datetime
,@TCA as money
,@TPA as money
,@PD as datetime
,@ApptID as varchar(10)

SELECT @ApptID = id 
	FROM [dbo].[Appointments] 
		WHERE  start_date = '20200608' and patient_person_id = '35576'

--EXEC GetAppointmentEfficiency (SELECT id FROM [dbo].[Appointments] WHERE start_date = '20200608' and patient_person_id = '35576')
EXEC GetAppointmentEfficiency @ApptID
,@DOS OUTPUT
,@CII2T OUTPUT
,@CII2I OUTPUT
,@CID OUTPUT
,@CDOS OUTPUT
,@TCA OUTPUT
,@TPA OUTPUT
,@PD OUTPUT;

PRINT @DOS
PRINT @CII2T
PRINT @CII2I
PRINT @CID
PRINT @CDOS
PRINT @TCA
PRINT @TPA
PRINT @PD

The EXEC using the declared variable @ApptID works.
The EXEC using the SELECT that populates the @ApptID to provide the same value does not.
I get the following error message: Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ','.

What am I missing? My intent is to get multiple records of output using the SELECT.

Also, I have tried to use temp tables to store these multiple rows but with no success.

2

you cant do that use a select as a parameter to pass to a stored procedure.

but are you trying to see if you can pass more than one row as parameter?

if so you can leverage TYPE

--sample data
drop table Appointments;
create table Appointments(id int identity(1,1), start_date date, 
patient_person_id int)
	

insert into Appointments
select top 100 DATEADD(d, column_id, GETDATE()),column_id
  from sys.all_columns 
  where column_id between 1 and 100

CREATE TYPE [dbo].[AppointmentType] AS TABLE(
	ApptID [int] NOT NULL
)

	alter proc GetAppointmentEfficiency
	@AppointmentType AppointmentType READONLY
	as
	begin

		select * 
		  from appointments a
		  join @AppointmentType b on a.id = b.ApptID
	end

DECLARE @AppointmentType AppointmentType

insert into @AppointmentType
select 1 union
select 99 union
select 33;

exec GetAppointmentEfficiency @AppointmentType
go
1 Like
3

you will have to do it One At a Time

for example
123
555

exec SP 123
exec SP 555

OR

instead of using an SP you can use CROSS APPLY ..

4

or another way


alter proc GetAppointmentEfficiency
	@AppointmentType nvarchar(max)
as
begin
	
	;with src
	as
	(
		select * 
		   from string_split(@AppointmentType,',')
	)
	select * 
		from appointments a
		join src b on a.id = b.value
end
go

DECLARE @Appointments nvarchar(max)
select @Appointments = '1,99,33'


exec GetAppointmentEfficiency @Appointments
go
5

I would recommend not using comma separated list. There are other ways to accomplish this. You can use a user defined table variable or XML as the input parameter

6 
alter proc GetAppointmentEfficiency
	@AppointmentType xml
as
begin
	
	;with src
	as
	(
		SELECT AppointmentId = C.value('(.)[1]', 'int') 
		FROM @AppointmentType.nodes('/Appointments/*') AS T(C)
	)
	select * 
		from appointments a
		join src b on a.id = b.AppointmentId
end
go



DECLARE @AppointmentType xml
select @AppointmentType = '<Appointments>  
							<AppointmentId>1</AppointmentId>
							<AppointmentId>33</AppointmentId>
							<AppointmentId>99</AppointmentId>
						  </Appointments>'


exec GetAppointmentEfficiency @AppointmentType
go
7

json , sure

alter proc GetAppointmentEfficiency
	@AppointmentType nvarchar(max)
as
begin
	
	;with src
	as
	(
			SELECT  *
			FROM OPENJSON(@AppointmentType)
			  WITH (
				id INT 'strict $.id',
				firstName NVARCHAR(50) '$.info.name',
				lastName NVARCHAR(50) '$.info.surname',
				age INT,
				dateOfBirth DATETIME2 '$.dob'
			  )
	)
	select * 
		from appointments a
		join src b on a.id = b.id
end
go


DECLARE @AppointmentType NVARCHAR(MAX);
SET @AppointmentType = N'[
  {"id": 1, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 33, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"},
  {"id": 99, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

exec GetAppointmentEfficiency @AppointmentType
go
1 Like