• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

sql server, passing table name dynamically to the query stmt.

I have the following code and I get all sort of errors related to the conversion of data type from varchar to int,...

now things like Weight is of type float in this table.

I do not know what is wrong but can not get it run. even when I exclude all problematic fields, I get error message related to execute line....

Any way to get his working?

Any other way of passing parameters to query?

thanks in advance

Declare 
  @loopcntr int
, @ID	 int
, @desName nvarchar(20)
, @srcName nvarchar(20)
, @Period  int
, @country nvarchar(4)
, @preloc  nvarchar(3)
, @strSql	nvarchar(4000)

Create Table #patient(
	emp_ID			int identity(1,1) not null
,	ID				int
,	srcName		nvarchar(30)
,	desName		nvarchar(30)
,	Country		nvarchar(3)
,   preloc      nvarchar(3)
,	period		int
)

Insert into #Empoyee
select ID, srcName, desName, Country , preloc , period
from TblSource where desName = 'Empoyee'


Set @loopcntr = @@ROWCOUNT 

While (@loopcntr <> 0)
Begin			
		set @loopcntr = 1
		Set @ID	= (Select ID From #Empoyee Where emp_ID = @loopcntr)
						
		if (@ID <> '')
		Begin
						
		Set @desName	= (Select [desname]	from #Empoyee Where emp_ID = @ID)		
		Set @srcName	= (Select [srcName]	from #Empoyee Where emp_ID = @ID)	
		Set @Period		= (select [Period]	from #Empoyee Where emp_ID = @ID)
		Set @country	= (select [country]	from #Empoyee Where emp_ID = @ID)
		Set @preloc	= (select [preloc]	from #Empoyee Where emp_ID = @ID)

Select @strSql = 'INSERT INTO MYDB.dbo.[' + @desName + '] (
		[EmployeeNum] 
	,	[Period]  
	,	[Location]	
	,   [AGE]
	,	[Gender_ID]
	,	[Weight]  
	,	[Salary]
	,	[Bonus1] 
	,	[Bonus2]
	,   [country]	 
)
	
	SELECT
		[employee#]
    ,   ' + ISNULL(@Period , '') + '
    ,   (' + @prefix + '[location])	AS site	
	,   [AGE]
	,	(CASE WHEN Gender = "F" THEN  1 ELSE  2 END)	AS [Gender]	
	,	' + ISNULL( + '[WEIGHT],' + null + ') AS [WEIGHT]
   ,	[Salary]
	,	[Bonus1] 
	,	[Bonus2]	
	,	' + @Country_Code + '
		
	From AI_DEV_Test.dbo.[' + @srcName + ']'

	Execute sp_executesql @strSql
	
	End
	set @loopcntr = @loopcntr -1 


End

Open in new window

0
shmz
Asked:
shmz
2 Solutions
 
HainKurtSr. System AnalystCommented:
CASE WHEN Gender = "F" THEN  
-->
CASE WHEN Gender = ''F'' THEN  

single " --> double '
0
 
HainKurtSr. System AnalystCommented:
this loop is infinite?

While (@loopcntr <> 0)
Begin                  
  set @loopcntr = 1 -- looks like you need to remove this first
  ...
  set @loopcntr = @loopcntr -1
end;

I guess it should be set to rowcount first (which you do I guess)
0
 
HainKurtSr. System AnalystCommented:
also

            Set @desName      = (Select [desname]      from #Empoyee Where emp_ID = @ID)            
            Set @srcName      = (Select [srcName]      from #Empoyee Where emp_ID = @ID)      
            Set @Period            = (select [Period]      from #Empoyee Where emp_ID = @ID)
            Set @country      = (select [country]      from #Empoyee Where emp_ID = @ID)
            Set @preloc      = (select [preloc]      from #Empoyee Where emp_ID = @ID)
-->

            Set @desName = [desname], @srcName      = [srcName], ... from #Empoyee Where emp_ID = @ID
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
HainKurtSr. System AnalystCommented:
and maybe this
Select @strSql = 'INSERT INTO MYDB.dbo.[' + @desName + '] (
		[EmployeeNum] 
	,	[Period]  
	,	[Location]	
	,   [AGE]
	,	[Gender_ID]
	,	[Weight]  
	,	[Salary]
	,	[Bonus1] 
	,	[Bonus2]
	,   [country]	 
) SELECT
		[employee#]
    ,   ''' + ISNULL(@Period , '') + '''
    ,   (' + @prefix + '[location])	AS site	
	,   [AGE]
	,	(CASE WHEN Gender = ''F'' THEN  1 ELSE  2 END) AS [Gender]	
	,	ISNULL([WEIGHT], null) AS [WEIGHT]
    ,	[Salary]
	,	[Bonus1] 
	,	[Bonus2]	
	,	' + @Country_Code + '
	From AI_DEV_Test.dbo.[' + @srcName + ']';

Open in new window

0
 
shmzAuthor Commented:
Hi HainKurt,

- set @loopcntr = 1 -- looks like you need to remove this first
wasn't suppose to be there just a typo.

-  Set @desName = [desname], @srcName      = [srcName], ... from #Empoyee Where emp_ID = @ID
Thanks for the tip..

- I'll try your query tomorrow and will get back to you.

Many thanks...It was so frustrating and you made it look so simple! :)
0
 
dqmqCommented:
typo alert:


            Set @desName = [desname], @srcName      = [srcName], ... from #Empoyee Where emp_ID = @ID


-->        Select @desName = [desname], @srcName      = [srcName], ... from #Empoyee Where emp_ID = @ID
0
 
Alpesh PatelAssistant ConsultantCommented:
Before execute just print and see the query is build proper. copy paste the query and execute it. it will show you all errors.

One thing when you uses inline query use ('') two single quote in place of single. and when you pass char values use 3 quotes.

0
 
HainKurtSr. System AnalystCommented:
please post the latest code each time with the error message...
0
 
shmzAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now