Is it possible to create a join between two table variables?
I tried doing something like this .. and it is throwing an error.
Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
Insert @.tstTableOne Values(1, 'Dummy1')
Insert @.tstTableOne Values(2, 'Dummy2')
Insert @.tstTableTwo Values(1, 'Test1')
Insert @.tstTableTwo Values(2, 'Test2')
Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from @.tstTableTwo
t where t.Sno = @.tstTableOne.Sno
Regards
PradeepTry:
UPDATE t1
SET t1.FirstName = t2.LastName
FROM @.tstTableOne t1
INNER JOIN @.tstTableTwo t2 ON t1.Sno = t2.Sno
Let me know how you get on.
Damien
"SqlBeginner" wrote:
> Is it possible to create a join between two table variables?
> I tried doing something like this .. and it is throwing an error.
> Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
> Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
> Insert @.tstTableOne Values(1, 'Dummy1')
> Insert @.tstTableOne Values(2, 'Dummy2')
> Insert @.tstTableTwo Values(1, 'Test1')
> Insert @.tstTableTwo Values(2, 'Test2')
> Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from @.tstTableTw
o
> t where t.Sno = @.tstTableOne.Sno
> Regards
> Pradeep|||See if this helps
Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
Insert @.tstTableOne Values(1, 'Dummy1')
Insert @.tstTableOne Values(2, 'Dummy2')
Insert @.tstTableTwo Values(1, 'Test1')
Insert @.tstTableTwo Values(2, 'Test2')
Update @.tstTableOne Set FirstName =LastName FROM
@.tstTableTwo t JOIN @.tstTableOne T1 ON t1.Sno =T.Sno
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:3ACD2367-C9D7-46F9-9938-88C278D6C9C0@.microsoft.com...
> Is it possible to create a join between two table variables?
> I tried doing something like this .. and it is throwing an error.
> Declare @.tstTableOne table ( Sno int, FirstName varchar(100) )
> Declare @.tstTableTwo table ( Sno int, LastName varchar(100) )
> Insert @.tstTableOne Values(1, 'Dummy1')
> Insert @.tstTableOne Values(2, 'Dummy2')
> Insert @.tstTableTwo Values(1, 'Test1')
> Insert @.tstTableTwo Values(2, 'Test2')
> Update @.tstTableOne Set @.tstTable.FirstName = t.FirstName from
> @.tstTableTwo
> t where t.Sno = @.tstTableOne.Sno
> Regards
> Pradeep|||Thanks it works. But any idea what is the bug in my Update statement?
Regards
Pradeep
"Damien" wrote:
> Try:
> UPDATE t1
> SET t1.FirstName = t2.LastName
> FROM @.tstTableOne t1
> INNER JOIN @.tstTableTwo t2 ON t1.Sno = t2.Sno
> Let me know how you get on.
> Damien
> "SqlBeginner" wrote:
>|||From BOL (UPDATE described):
"A table alias specified in a FROM clause cannot be used as a qualifier in
SET column_name"
You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
which allows you to specify multiple tables in a FROM clause. When using th
e
T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not mi
x
aliased tables and non-aliased tables.
So,
UPDATE t
NOT UPDATE your_table
unless you are only updating one table, in which case "UPDATE your_table" is
correct.
Hope that helps!
Damien
"SqlBeginner" wrote:
> Thanks it works. But any idea what is the bug in my Update statement?
> Regards
> Pradeep
> "Damien" wrote:
>|||IMHO while using Joins in Table variables by definition or by rule we need t
o
use Alias names only. Since pradeep wasn't using that it throwed an error.
If at all he had tried this with Temp tables his logic would have worked as
it is without any hassles. Bcoz Temp tables doesn't have that rule.
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"Damien" wrote:
> From BOL (UPDATE described):
> "A table alias specified in a FROM clause cannot be used as a qualifier in
> SET column_name"
> You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
> which allows you to specify multiple tables in a FROM clause. When using
the
> T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not
mix
> aliased tables and non-aliased tables.
> So,
> UPDATE t
> NOT UPDATE your_table
> unless you are only updating one table, in which case "UPDATE your_table"
is
> correct.
> Hope that helps!
>
> Damien
> "SqlBeginner" wrote:
>|||This is what I meant,
Create table #tstTableOne table ( Sno int, FirstName varchar(100) )
Create table #tstTableTwo table ( Sno int, FirstName varchar(100) )
Insert #tstTableOne Values(1, 'Dummy1')
Insert #tstTableOne Values(2, 'Dummy2')
Insert #tstTableTwo Values(1, 'Test1')
Insert #tstTableTwo Values(2, 'Test2')
Update #tstTableOne Set #tstTableOne.FirstName = t.FirstName from
#tstTableTwo t where t.Sno = #tstTableOne.Sno
Select * from #tstTableOne
Select * from #tstTableTwo
Drop table #tstTableOne
Drop table #tstTableTwo
Best Regards
Vadivel
http://vadivel.blogspot.com
"Damien" wrote:
> From BOL (UPDATE described):
> "A table alias specified in a FROM clause cannot be used as a qualifier in
> SET column_name"
> You're mixing ANSI syntax with the T-SQL extension for UPDATEs and DELETEs
> which allows you to specify multiple tables in a FROM clause. When using
the
> T-SQL syntax I _always_ specify the alias in the UPDATE clause and do not
mix
> aliased tables and non-aliased tables.
> So,
> UPDATE t
> NOT UPDATE your_table
> unless you are only updating one table, in which case "UPDATE your_table"
is
> correct.
> Hope that helps!
>
> Damien
> "SqlBeginner" wrote:
>
Showing posts with label declare. Show all posts
Showing posts with label declare. Show all posts
Monday, March 12, 2012
Wednesday, March 7, 2012
join stored procedure and view
Hi everybody
I have this stored procedure called flydate
CREATE PROCEDURE FlyDate AS
declare @.gencalendar table (cal_date datetime primary key)
declare @.p_date datetime
set @.p_date =getdate()
while @.p_date > DateAdd(mm, -3, GetDate()) BEGIN
insert into @.gencalendar(cal_date)
VALUES(@.p_date)
--getdate
SET @.p_date = DateAdd(d, -1, @.p_date)
END
select cal_date AS KF_DATE,0 AS KF_STATUS from @.gencalendar
GO
-which returns all the date for the past three month
and this is my view
CREATE VIEW dbo.rpt_Kids
AS
SELECT TOP 100 PERCENT
KF_ID,dbo.just_date_formal(KF_DATE) as KF_DATE,KF_STATUS FROM dbo.KIDS
order by Year(KF_date) DESC,Month(KF_date) DESC,Day(KF_date) DESC
just_date_formal function
CREATE FUNCTION [dbo].[just_date_formal](@.dtvalue datetime)
RETURNS nvarchar(40)
AS
BEGIN
DECLARE @.display nvarchar(40)
SET @.display = CAST(DATEPART(dd, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(mm, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(yyyy, @.dtvalue) AS nvarchar) + ', ' + CAST(DATENAME(dw, @.dtvalue) AS nvarchar)
RETURN @.display
END
this returns all the date on which error is generated.on other dates on which no error message is generated.
now i want to join both of them so that i get dates of error message and those dates also on which error message is not generated
like
23 june 2005 error
22 june 2005
21 june 2005 error
it should return all dates regardless errror is there or not
Hi,
You directly cannot JOIN the output of a stored procedure in a FROM clause. Stored procedures cannot be used in contexts that require relational expressions. I realize this seems odd at first, since you *can* return a rowset from a stored procedure. However, the reasons we disallow it in TSQL are
(a) The shape of the rowset cannot be determined ahead of time (there is no metadata anywhere that describes the table returned by a stored proc, and besides, the shape returned might depend on the run-time execution path though the proc ... if (condition) select * from somewhere else select * from somewherelese)
and (b) a stored procedure can return > 1 result set.
You have a few options available
1) You can capture the output of the stored procedure into a temporary table using the INSERT INTO EXEC syntax. The code would looks something like:
create table #tempcal(KF_DATE datetime, KF_STATUS int)
go
insert into #tempcal exec FlyDate
go
now you can JOIN on #tempcal.
2) You can refactor your proc to make it into a table-valued function, which you *can* use in a JOIN. You will have to factor-out the non-deterministic getdata() and pass them in as parameters. The code would look something like this
create function FlyDateFunc(@.p_date datetime, @.p_today datetime)
returns @.gencalendar table (KF_DATE datetime primary key, KF_STATUS int)
as
begin
while @.p_date > DateAdd(mm, -3, @.p_today)
begin
insert into @.gencalendar values (@.p_date, 0)
set @.p_date = DateAdd(d, -1, @.p_date)
end
return
end
go
select * from FlyDateFunc(getdate() , getdate())
go
Does this answer your question?
Thanks
Hi,
I have an extra complication to this problem. I have SP's that create dynamic columns based on the parameters they get. So i don't know in advance what my #table should look like. Is there any way to do this without knowing the columns of the #table? (like the select * into #tmp from table1 but then using EXEC? )
I have developed SP's that create dyn columns. Now i want to use these same SP's in SSRS, but that want's to know the column names in advance. So i want to store the results of the SP in a #table and then unpivot that to send it to RS. Think that'll work?
[edit] the SP's use dynamic SQL to get the results
Regards Gert-Jan
join stored procedure and view
Hi everybody
I have this stored procedure called flydate
CREATE PROCEDURE FlyDate AS
declare @.gencalendar table (cal_date datetime primary key)
declare @.p_date datetime
set @.p_date =getdate()
while @.p_date > DateAdd(mm, -3, GetDate()) BEGIN
insert into @.gencalendar(cal_date)
VALUES(@.p_date)
--getdate
SET @.p_date = DateAdd(d, -1, @.p_date)
END
select cal_date AS KF_DATE,0 AS KF_STATUS from @.gencalendar
GO
-which returns all the date for the past three month
and this is my view
CREATE VIEW dbo.rpt_Kids
AS
SELECT TOP 100 PERCENT
KF_ID,dbo.just_date_formal(KF_DATE) as KF_DATE,KF_STATUS FROM dbo.KIDS
order by Year(KF_date) DESC,Month(KF_date) DESC,Day(KF_date) DESC
just_date_formal function
CREATE FUNCTION [dbo].[just_date_formal](@.dtvalue datetime)
RETURNS nvarchar(40)
AS
BEGIN
DECLARE @.display nvarchar(40)
SET @.display = CAST(DATEPART(dd, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(mm, @.dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(yyyy, @.dtvalue) AS nvarchar) + ', ' + CAST(DATENAME(dw, @.dtvalue) AS nvarchar)
RETURN @.display
END
this returns all the date on which error is generated.on other dates on which no error message is generated.
now i want to join both of them so that i get dates of error message and those dates also on which error message is not generated
like
23 june 2005 error
22 june 2005
21 june 2005 error
it should return all dates regardless errror is there or not
Hi,
You directly cannot JOIN the output of a stored procedure in a FROM clause. Stored procedures cannot be used in contexts that require relational expressions. I realize this seems odd at first, since you *can* return a rowset from a stored procedure. However, the reasons we disallow it in TSQL are
(a) The shape of the rowset cannot be determined ahead of time (there is no metadata anywhere that describes the table returned by a stored proc, and besides, the shape returned might depend on the run-time execution path though the proc ... if (condition) select * from somewhere else select * from somewherelese)
and (b) a stored procedure can return > 1 result set.
You have a few options available
1) You can capture the output of the stored procedure into a temporary table using the INSERT INTO EXEC syntax. The code would looks something like:
create table #tempcal(KF_DATE datetime, KF_STATUS int)
go
insert into #tempcal exec FlyDate
go
now you can JOIN on #tempcal.
2) You can refactor your proc to make it into a table-valued function, which you *can* use in a JOIN. You will have to factor-out the non-deterministic getdata() and pass them in as parameters. The code would look something like this
create function FlyDateFunc(@.p_date datetime, @.p_today datetime)
returns @.gencalendar table (KF_DATE datetime primary key, KF_STATUS int)
as
begin
while @.p_date > DateAdd(mm, -3, @.p_today)
begin
insert into @.gencalendar values (@.p_date, 0)
set @.p_date = DateAdd(d, -1, @.p_date)
end
return
end
go
select * from FlyDateFunc(getdate() , getdate())
go
Does this answer your question?
Thanks
Hi,
I have an extra complication to this problem. I have SP's that create dynamic columns based on the parameters they get. So i don't know in advance what my #table should look like. Is there any way to do this without knowing the columns of the #table? (like the select * into #tmp from table1 but then using EXEC? )
I have developed SP's that create dyn columns. Now i want to use these same SP's in SSRS, but that want's to know the column names in advance. So i want to store the results of the SP in a #table and then unpivot that to send it to RS. Think that'll work?
[edit] the SP's use dynamic SQL to get the results
Regards Gert-Jan
Subscribe to:
Posts (Atom)