Solved

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

Posted on 2011-09-26
9
224 Views
Last Modified: 2012-05-12
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
Comment
Question by:shmz
9 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 36658705
CASE WHEN Gender = "F" THEN  
-->
CASE WHEN Gender = ''F'' THEN  

single " --> double '
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36659678
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 36660446
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
 
LVL 51

Accepted Solution

by:
HainKurt earned 450 total points
ID: 36662994
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:shmz
ID: 36675049
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
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 50 total points
ID: 36677898
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36707641
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 36710799
please post the latest code each time with the error message...
0
 

Author Closing Comment

by:shmz
ID: 36899844
Thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now