-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
View Complete Post
create table #tmp1(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into #tmp1(rowID) SELECT ' + @rowID)
This does not work
Declare @tmp1 table(rowID int)
declare @rowID int
set @rowID = 1
exec('insert into @tmp1(rowID) SELECT ' + @rowID)
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tmp1'.
Is there a way to use Dynamic Sql against a Table var -- @tmp1?
How to do this?
The goal is to be able to pass in a string param like this to a proc
s1 = 'aa'',''bb'',''cc'
declare @s1 varchar(20)
exec('Insert Into ' + @tmp1 + ' (...) Where fldx In (''' + @s1 + ''')')
I am well versed with Join/Outer Join/Inner Join. My question is not related to normal Joining of two tables. Its little complex. Let me see if I can explain well.
There are two tables, were data is stored.
Name_Info : nvarchar(10)
Date_Info : smalldate()
Spec_Info_1 : tinyint
Spec_Info_2 : tinyint
Spec_Info_3 ; tinyint
Name_Data : nvarchar(10)
City_Data : nvarchar(10)
State_Data : nvarchar(10)
To join these two tables, I use the following query (joined on Name_Info ON Name_Data)
SELECT Name, Date=CONVERT(varchar, Date, 101), City, State,
CASE Spec_Info_1 WHEN '0' THEN 'House' WHEN '2' THEN 'Shop' WHEN '3' THEN 'Bay' ELSE '-'
END AS Spec_Info_1,
CASE Spec_Info_2 WHEN '0' THEN '10 Feet' WHEN '2' THEN '20 Feet' WHEN '3' THEN '40 Feet' ELSE '-' END AS Spec_Info_2,
CASE Spec_Info_3 WHEN '0' THEN '2 Windows' WHEN '2' THEN '4 Windows' WHEN '3' THEN '6 Windows' ELSE '-'
END AS Spec_Info_3
FROM Table_Spec_Data LEFT OUTER JOIN Table_Name_Data On Table_Spec_Data.Name_Info=Table_Name_Data.Name_Data
I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted
to a @temp table variable.
Here are some steps I have already taken...
The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."
Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.
Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!
The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.
The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).
Does anyone have any suggestions (or hope) for me?
I am probably missing something obvious here; -
I have created a package with a variable named; - ServerName and a value of 'KIERAN-PC'
I exported this variable as an SSIS configuration within a SQL Server table
I noticed the following record appeared within the SQL Server configuration table.
I know it’s possible to loop through table records using a variable and a For Each Loop Task. But is there a way to use the values in the variable and use them in a File System Task? When I tried, the File System Task complains that the variable is
not a string. Any thoughts?
Objective – I need to loop though column values in a table which has the paths to the files that I’ll need to move using File System Task.
The table only has one column which stores the paths to the files to be moved.
Thanks in advance.
anybody help me, i wanna create function using table name parameter.
this is my function code
ALTER function [dbo].[CreateRecID]( @TableName varchar(100))
declare @RecID bigint
declare @Sql varchar(2000)
set @Sql = (select case when max(RecID) is null then 0 else max(RecID) + 1 end as RecID from @TableName)
when i run this code
i got error "Must declare the table variable "@TableName".
I have already declare a @TableName Parameter.
can you show me the way to solve my problem?
Thanks so much
I am a learning-as-I-go person without formal training. I have been working with Access databases for about 10 years, and have begun working with SQL in the last 5 years or so. The system in user here is a 2K3 server (8xXeon 2.8Ghz, 12GB RAM,
1TB HDD) running SQL Server 2005 (32bit). I am trying to develop a SQL query (view) which looks for values of one table' field at specific times based on a table of those times. When I use a datetime variable, that particular value at
that particular datetime will be returned. When I try to replace the variable with the datetime result of a second query(view), no data is returned. The code is below.
SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2010-09-30 04:59:00' -- Need to replace this value with the the list of 'PriVolCk' datetimes