Using NULL in fixed query

[Former Member]

Hey guys

I have a stored procedure that accepts a number of parameters that I am calling from a query template fixed query.

EXECUTE [spr_MiiEventLog_Get] [Param.1], [Param.2] ,[Param.3],'[Param.4]',[Param.5]

I want to be able to pass NULL into Param 4.

Problem is that becuase the Parameter is a string it needs to be quoted, but NULL doesnt need to be quoted.

I could move the requirement to "quote" strings externally to the query but that doesnt make sense as it makes it unusable without prior knowledge.

Any suggestions ?

[sidnooradarsh]

I assume you are using Oracle DB, in that case,

Create your Stored Procedure such that input params are default to NULL (or any desired value) then use either the Positional or Named notation to pass values for required input params of Stored Procedure in Query template.

This should solve the problem of passing NULL because when you don't pass any value to respective input variable then Stored Procedure will take the Default Value.

Hope this helps!!

Regards,

Adarsh

[Former Member]

Hi Adarsh

I am using SQL server and am already doing as suggested with the stored proc.

What i dont undertsand is how to NOT pass a parameter in the query template

My understanding is that the everythign I pass will get replaced in the query string.

so if I dont pass anything it will be a blank string which is an invalid query.

and NULL will bne quoted as it is quoted in my fixed query

Can you explain what you mean by

 the Positional or Named notation to pass values for required input params of Stored Procedure in Query template 

[sidnooradarsh]

Hello,

I hope I am understanding your query...

the Positional or Named notation to pass values for required input params of Stored Procedure in Query template

The concepts of Positional or Named notation is similar in both SQL server and Oracle expect for the syntax,

Positional:-

Is something which we normally use to pass params to SP like,

Exec procedure_name param1, param2....paramN, [output params is any] 

Named:-

Is something you specify the input parameter name being used in SP like,

Exec procedure_name param1= value1, param2 = value2....paramN = valueN, [output params is any] 

For example say your SP looks like this,

Create PROC ParamPassTest
(
   @var1 varchar(50) = NULL, //input param defaulted to NULL means not mandatory to pass input value
   @var2 varchar(50) = NULL 
  ) 
  as
//Table variable declaration
DECLARE @Temp_MsgTable TABLE
(
MessageID int IDENTITY(1,1),
Message varchar(100)
) 
begin
 if  @var1 is NULL
begin 
insert into @Temp_MsgTable values(('Only Var2=' +  @var2))
end
if  @var2 is NULL
begin
insert into @Temp_MsgTable values(('Only Var1=' +  @var1))
end
if @var1 is NOT NULL and @var2 is NOT NULL
begin
insert into @Temp_MsgTable values((@var1 + ' ' +  @var2))
end
select Message from @Temp_MsgTable
end

In Query Template,

Positional Call:-

exec dbo.ParamPassTest 'Hello' 

Will display Output :Only Var1=Hello

Inputs will be taken in the order they are created in SP

Named Call:-

 exec dbo.ParamPassTest @var2 = 'World' 

Will display Output :Only Var2=World

Independent of position since we are directly mapping to input parameter name itself.

Hope this helps!!

Regards,

Adarsh

[Former Member]

Thanks guys

what you have said makes sense.

In essence I am using a combination of the two approaches.

It seems that you have to decide outside of the query template whether to quote things or not,

and using the Postional approach really makes things easier to manage.

Thanks again

Chris

[Former Member]

Chris,

what about this: use a stringif in your assignment to the Param.4 which fills either NULL or the character value, surrounded by single quotes. So you can omit the quotes in the query definition.

stringif( value == "", "NULL", "'" & value & "'" )

Michael