problem setting variable dynamically and executing dynamic query

Question

Hi All,

I am facing problem with stored procedure where i am setting variable value dynamically by select statement, the stored procedure mentioned below:

 

ALTER PROCEDURE [dbo].[SP_UpdateFpaByFiAdmin](
	-- Add the parameters for the stored procedure here
	@EMP_ID VARCHAR(15),
	
@STRING_Employee_FPA_distribution  VARCHAR(MAX) = ''

	)
AS
BEGIN 
DECLARE @ECODE AS VARCHAR(15)
DECLARE @BU AS VARCHAR(15)
DECLARE @FPA_CYCLE_ID bigint
DECLARE @HEAD_ID1 VARCHAR(max)
DECLARE @STRING_Employee_FPA_distribution_QUERY AS VARCHAR(max)
DECLARE @HEAD_ID_QUERY as varchar(max)


--REQUIRED VALUES
SET @FPA_CYCLE_ID=(select max(fpa_cycle.fpa_cycle_id)from fpa_cycle inner join fpa_main on fpa_cycle.fpa_id=fpa_main.fpa_id where fpa_main.fpa_id=(select max(fpa_id) from fpa_main where emp_id= @EMP_ID))

SET @HEAD_ID1 = 'SELECT [HEAD_ID] FROM ('+@STRING_Employee_FPA_distribution+') as x where x.[EMP_ID] = '+@EMP_ID+''
	SET NOCOUNT ON;
	BEGIN TRY
	    begin tran
--[UPDATE ].[Employee_FPA_Distribution] 
--Parmamers:@EMP_ID,@HEAD_ID,@CLAIMED_AMNT IN TABLE
		
if(@STRING_Employee_FPA_distribution != null or @STRING_Employee_FPA_distribution != '')
BEGIN
SET @STRING_Employee_FPA_distribution_QUERY='UPDATE Employee_FPA_Distribution SET 
Already_Claimed = isnull(Already_Claimed,0)+MAIN.[CLAIM_AMNT] FROM Employee_FPA_Distribution EFD INNER JOIN ( '+ @STRING_Employee_FPA_distribution+') MAIN ON EFD.FPA_HEAD_ID=MAIN.[HEAD_ID] WHERE FPA_CYCLE_ID='+@FPA_CYCLE_ID
EXEC (@STRING_Employee_FPA_distribution_QUERY)
END
  COMMIT
   SELECT 1

   END TRY
	BEGIN CATCH
		ROLLBACK
		SELECT  ERROR_MESSAGE () 
		
	END CATCH
	
END 

When i am setting @FPA_CYCLE_ID, the value is not getting set to variable ..

and also i am facing problem when executing EXEC (@STRING_Employee_FPA_distribution_QUERY) ..after this wrote SELECT (@STRING_Employee_FPA_distribution_QUERY to test what query is set in this variable ..it returns null.

In @STRING_Employee_FPA_distribution i pass select statement to this stored procedure

could anyone pls help where i go wrong when i am setting variable and when i execure dynamic query..

Pls advise..

 

---

-- Regards Sandeep

Answer 1

use the print statement and print that dynamic query that's executing, then observe that printed query.
print @STRING_Employee_FPA_distribution_QUERY
keep above statement above the exec statement and check that printed query... if u r not able to spot, post that printed query here..

Answer 2

If @FPA_CYCLE_ID isn't being set by a SELECT, then the SELECT isn't returning any rows.   Also, you can't test NULL by using equals or not equals like you're trying to do; you must use "IS NULL" or "IS NOT NULL".    Finally, your EXEC string is null because one or more variables you're concatenating in it are null, and nulls propagate.

---

Michael Asher

Answer 3

hi,

i used PRINT @STRING_Employee_FPA_distribution_QUERY but it returned null..

this i think b'cus @FPA_CYCLE_ID is not returning anything.. i wrote PRINT @FPA_CYCLE_ID but the value is null..

then i copied the query which sets fpa_cycle_id and executed on other query window it works fine ..

could you please advise where i am going wrong when setting FPA_CYCLE_ID

---

-- Regards Sandeep

Answer 4

That means the below query is returning "null". check y the below query is returning?? is it query problem? and also
u need to decide, is it possible to return "null" values in your business case??
SET @FPA_CYCLE_ID=(select max(fpa_cycle.fpa_cycle_id)from fpa_cycle inner join fpa_main on
fpa_cycle.fpa_id=fpa_main.fpa_id where fpa_main.fpa_id=(select max(fpa_id) from fpa_main where emp_id= @EMP_ID))
Above query is not returning any rows.

Answer 5

Do you mean this statement?

SET @FPA_CYCLE_ID=(select max(fpa_cycle.fpa_cycle_id)from fpa_cycle inner join fpa_main on fpa_cycle.fpa_id=fpa_main.fpa_id where fpa_main.fpa_id=(select max(fpa_id) from fpa_main where emp_id= @EMP_ID))

You're printing the value of @FPA_CYCLE_ID immediately after the SET, and it's null? If you're running the query separately and it runs a non-null value, then I'm guessing you have a problem with the @EMP_ID parameter being passed into the SP. Print that before the SET and ensure it's what you think it should be.

---

Michael Asher

Answer 6

Hi ,

many thanks for your repllies ..ya i should check with @EMP_ID first..

Only thing i want to check is there any problem with query

SET @STRING_Employee_FPA_distribution_QUERY='UPDATE Employee_FPA_Distribution SET 
			Already_Claimed = isnull(Already_Claimed,0)+MAIN.[CLAIM_AMNT]
			FROM Employee_FPA_Distribution EFD INNER JOIN ( '+ @STRING_Employee_FPA_distribution+')
			 MAIN ON EFD.FPA_HEAD_ID=MAIN.[HEAD_ID] WHERE FPA_CYCLE_ID='+@FPA_CYCLE_ID
			EXEC (@STRING_Employee_FPA_distribution_QUERY)

..i mean would this be the correct way to write dynamic query..

b'cus when debugging i wrote simply inside if condition

UPDATE Employee_FPA_Distribution SET 
Already_Claimed = isnull(Already_Claimed,0)+MAIN.[CLAIM_AMNT] FROM Employee_FPA_Distribution EFD INNER JOIN (@STRING_Employee_FPA_distribution) MAIN ON EFD.FPA_HEAD_ID=MAIN.[HEAD_ID] WHERE FPA_CYCLE_ID= @FPA_CYCLE_ID
EXEC (@STRING_Employee_FPA_distribution_QUERY)

..and an error returned when executing stored procedure .."must declare table variable

@STRING_Employee_FPA_distribution"

Pls Advise..

 

---

-- Regards Sandeep

Answer 7

Your first way is correct.Concatenating that @FPA_CYCLE_ID to the query. However your second way, moving
@FPA_Cycle_ID inside will gives you the error. In that case,you need to use like below.
exec sp_executesql @yourquery,N'variables declaration',N'variable values'
see the below URL.
http://msdn.microsoft.com/en-us/library/ms175170.aspx

Answer 8

Hi,

Many thanks for your rplies..

I will try as suggested and get back to you..if i face any problem..

 

---

-- Regards Sandeep

Answer 9

Hi,

I sorted the sql query for variable @FPA_Cycle_ID  ..but it still gives error in catch by running error_message() as "Error converting data type varchar to bigint."

..by running below mentioned command

 

SET

 

@STRING_Employee_FPA_distribution_QUERY='UPDATE Employee_FPA_Distribution SET

Already_Claimed = isnull(Already_Claimed,0)+MAIN.[CLAIM_AMNT]

FROM Employee_FPA_Distribution EFD INNER JOIN ( '

 

+ @STRING_Employee_FPA_distribution+')

MAIN ON EFD.FPA_HEAD_ID=MAIN.[HEAD_ID] WHERE FPA_CYCLE_ID= '

 

+ @FPA_CYCLE_ID

could you please advise what would have gone wrong....

 

 

 

---

-- Regards Sandeep

Answer 10

you need to cast @FPA_CYCLE_ID to nvarchar before adding to that string.

SET @STRING_Employee_FPA_distribution_QUERY='UPDATE Employee_FPA_Distribution SET Already_Claimed = isnull(Already_Claimed,0)+MAIN.[CLAIM_AMNT] FROM Employee_FPA_Distribution EFD INNER JOIN ( '
+ @STRING_Employee_FPA_distribution+') 
MAIN ON EFD.FPA_HEAD_ID=MAIN.[HEAD_ID] WHERE FPA_CYCLE_ID= ' 
+ cast( @FPA_CYCLE_ID as nvarchar(20))

Answer 11

Why do you want to embed the value into the sting rather than using sp_ExecuteSQL and parameter?

E.g.

SET @STRING_Employee_FPA_distribution_QUERY='UPDATE Employee_FPA_Distribution 

SET Already_Claimed = isnull(Already_Claimed,0)+MAIN.[CLAIM_AMNT] 

FROM Employee_FPA_Distribution EFD INNER JOIN ( '
+ @STRING_Employee_FPA_distribution+') 
MAIN ON EFD.FPA_HEAD_ID=MAIN.[HEAD_ID] WHERE FPA_CYCLE_ID= @FPA_CYCLE_ID'

execute sp_ExecuteSQL, @String_Employee_FRA_distribution,
N'@FPA_CYCLE_ID bigint', @FPA_CYCLE_ID

 

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog