Dynamic table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic table

Author

Topic

lakon15
Starting Member

12 Posts

Posted - 2007-12-18 : 04:00:49

Hi All, Iam new bie in SQL server,
I want to create store procedure like this, but it seem that this query not running.
My Point is I can check the existing data from dynamic table.
to execute another procedure ie update and insert

Create procedure [dbo].[spAddLogDataByDepth]
@columnName varchar(256) ,
@columnValue Real ,
@uidPath varchar(325) ,
@columnIndex varchar(64) ,
@indexNo Real

As
Begin
DECLARE @query varchar(1000),
@query2 varchar(1000),
@pathHash bigint

-- convert path to hash
Set @pathHash= [dbo].fnFNVHash(@uidPath)

-- Check existing data
Set @query2='Select '+cast(@columnName as varchar(64))+' FROM WMLogData_'+cast(@pathHash as Varchar(10))+' Where '+@columnIndex+' = '+cast(@indexNo as varchar(64))

IF (@query2 is Not Null)

-- Update Log data
Begin
Select @query='
Update WMLogData_'+cast(@pathHash as Varchar(64))+' Set '+cast(@columnName as Varchar(64))+' = '+cast(@ColumnValue as varchar(64))+'
Where '+cast(@ColumnIndex as Varchar(64))+' = '+cast(@indexNo as varchar(64))
End
else

-- Insert Log Update
Begin
Select @query='Insert WMLogData_'+cast(@pathHash as Varchar(64))+' ('+cast(@columnIndex as Varchar(64))+','+cast(@columnName as Varchar(64))+') values ('+cast(@indexNo as varchar(64))+','+cast(@ColumnValue as varchar(64))+')'
end

End

exec(@query)

Is there any suggestion for this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 04:21:44

Can you add some Print statements in b/w and check whether the query strings built are as per the correct syntax.

like Print @query

Also what are errors you are getting? Whats the purpose of variable @query2?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 04:41:49

why do you want to pass object names as parameter?

Madhivanan

Failing to plan is Planning to fail

lakon15
Starting Member

12 Posts

Posted - 2007-12-18 : 04:56:33

Hi All, I'm Already using Print(@Query) to help is the result is correct or not, But every time I'm passing all parameter The statement "IF (@query2 is Not Null)" always define as not null so the next procedure always doing Update process,
What I want to is, if we passing all parameter that give result of @query2 is null, then process Insert, and if the result is not null process Update

Thank's before

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 05:02:16

The string @query2 will be always not null when you pass valid values for all params. What you are probably interested might be result of executing the string @query2. In that case try like this

IF EXISTS (Exec (@query2))

rather than

IF (@query2 is Not Null)

lakon15
Starting Member

12 Posts

Posted - 2007-12-18 : 05:07:51

Still having an error
Msg 156, Level 15, State 1, Procedure spAddLogDataByDepth, Line 21
Incorrect syntax near the keyword 'Exec'.
Msg 102, Level 15, State 1, Procedure spAddLogDataByDepth, Line 21
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Procedure spAddLogDataByDepth, Line 29
Incorrect syntax near the keyword 'else'.

Update WMLogData_2722731946 Set [DXC] = 123
Where [Mdepth] = 500

and also give the incorrect result, when I put this parameter it sould be appear Insert data not update data

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2007-12-18 : 05:11:08

Hi,
Set your session property for Concat_null_yields_null to ON.
That might help.
SET CONCAT_NULL_YIELDS_NULL {ON | OFF}

Thanks!
Raju
http://rajusqlblog.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 05:15:57

Try putting it into temp table,get count and compare as

INSERT into #temp
Exec (@query2)

Declare @n int
SELECT @n=COUNT(*)
FROM #temp

IF @n>0
UPDATE...
ELSE
INSERT...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 05:28:35

Also, you can't search be COLUMNINDEX. It must be with COLUMNNAME.
If you want COLUMNINDEX to work, you have to fetch the column name from INFORMATION_SCHEMA.COLUMNS with COLUMNINDEX first.

E 12°55'05.25"
N 56°04'39.16"

lakon15
Starting Member

12 Posts

Posted - 2007-12-18 : 21:42:19

Dear All
Thank's a lot
It' successfull when insert #temp table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources