cascading deletes through a 1-M join table

Hi all,
I have three tables, two with unique integer PK's and a join table for a 1-M
join.
PARENT JOIN CHILD
+--+ +--+--+ +--+
+ 7 + --> + 7 + 45 + --> + 45 +
+--+ +--+--+ +--+
+ 7 + 197 + --> + 197 +
+--+--+ +--+
I want to be able to delete a PARENT record and have it clean up the JOIN
and all CHILD entries.
The join table is the Foreign key table. If I set up cascade delete from
Parent to JOIN that works, but i can't set up cascade delete from JOIN to
CHILD because the keys in the JOIN table are not primary keys.
I know that I can do this in a trigger on JOIN but this is a natural
straight parent->child delete, is there a way to cascade delete through the
JOIN to the CHILD?
thanks!
John"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23A0gjCoUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have three tables, two with unique integer PK's and a join table for a
> 1-M join.
> PARENT JOIN CHILD
> +--+ +--+--+ +--+
> + 7 + --> + 7 + 45 + --> + 45 +
> +--+ +--+--+ +--+
> + 7 + 197 + --> + 197 +
> +--+--+ +--+
> I want to be able to delete a PARENT record and have it clean up the JOIN
> and all CHILD entries.
> The join table is the Foreign key table. If I set up cascade delete from
> Parent to JOIN that works, but i can't set up cascade delete from JOIN to
> CHILD because the keys in the JOIN table are not primary keys.
> I know that I can do this in a trigger on JOIN but this is a natural
> straight parent->child delete, is there a way to cascade delete through
> the JOIN to the CHILD?
>
Create a unique index on the JOIN table to support the relationship to
CHILD.
David

capturing dynamically returned integer

Hello there.
I have a dynamic SQL query (I had to use it - I swear!) that returns a
single integer value. However, I'm having trouble capturing the value in a
local variable. Any ideas what I should do with my syntax (to get it working
I mean...)?
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = 'select max(Column1) from table1'
--execute sp_executesql @.testSQL --this returns an integer value
--the following gives errors
select @.IntegerVar = (execute sp_executesql @.testSQL)
--the following assigns 0 to @.IntegerVar rather than the query result
execute @.IntegerVar = sp_executesql @.testSQLhttp://www.aspfaq.com/2492
"len" <len@.discussions.microsoft.com> wrote in message
news:B254F6C4-E94F-48B6-BB9C-7C03066D6998@.microsoft.com...
> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it
> working
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL|||Try,
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = N'select @.IntegerVar = max(Column1) from table1'
execute sp_executesql @.testSQL, N'@.IntegerVar int output', @.IntegerVar outpu
t
select @.IntegerVar
go
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/defaul...B;EN-US;q262499
AMB
"len" wrote:

> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it worki
ng
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL|||Hi Len
Try something like:
declare @.SQLQuery nvarchar(1024)
declare @.IntegerVar integer
select @.SQLQuery = 'select @.MaxVar = max(Column1) from table1'
execute sp_executesql @.SQLQuery, N'@.MaxVar int OUTPUT', @.IntegerVar OUTPUT
SELECT @.IntegerVar
John
"len" wrote:

> Hello there.
> I have a dynamic SQL query (I had to use it - I swear!) that returns a
> single integer value. However, I'm having trouble capturing the value in a
> local variable. Any ideas what I should do with my syntax (to get it worki
ng
> I mean...)?
> declare @.SQLQuery nvarchar(1024)
> declare @.IntegerVar integer
> select @.SQLQuery = 'select max(Column1) from table1'
> --execute sp_executesql @.testSQL --this returns an integer value
> --the following gives errors
> select @.IntegerVar = (execute sp_executesql @.testSQL)
> --the following assigns 0 to @.IntegerVar rather than the query result
> execute @.IntegerVar = sp_executesql @.testSQL

Can't you have a variable TOP in a select statement?

Hi,
I got a stored procedure like this
CREATE PROCEDURE dbo.readImport
(
@.Start INTEGER,
@.Number INTEGER
)
AS
SELECT TOP @.Number * FROM Import WHERE RowID >= @.Start ORDER BY RowID
GO
However, it doesn't seem to like having an unknown @.Number.
Any ideas?
MortenHi Morten,
If you are using SQL 2k its not possible.
The only thing is to use dynmiac sql for that.
HTH, Jens Suessmeyer.|||Ok, thanks
Morten
On Fri, 11 Nov 2005 09:30:25 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>|||... or SET @.@.ROWCOUNT...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131697825.522144.101160@.g49g2000cwa.googlegroups.com...
> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>

Can't use parameter with DateAdd Function?

Hi, I encountered a strange error today. I have an Integer parameter "hours" that I am trying to use in my SQL Query.

DATEADD(hh, @.hours, @.startTime)

It works if I have it set such as DATEADD(hh, 8, @.startTime), but I need that parameter there for what I need.

I get this error:

Error Source: System.Data
Error Message: Failed to convert parameter value from Decimal to DateTime. I tried a variety of CInt and other conversion functions to no avail.

Any ideas?

Hello,

Try this:

=DateAdd("h", CDbl(@.hours), @.startTime)

Hope this helps.

Jarret

|||Just tried using this with no luck in the following expression. My goal is to use this with a report parameter that sets the value of @.period to 1, 7, 30, 90, or 365.

SELECT *
FROM dbo.v_call
WHERE (dbo.v_call.entry_date >= DATEADD(d, CDbl(@.period), GETDATE()))

When running the query I get the following error; string was not recognized as a valid date time.

I try changing my query to

SELECT *
FROM dbo.v_call
WHERE (dbo.v_call.entry_date >= DATEADD(d, @.period, GETDATE()))

and get this error:

Failed to convert parameter value from decimal to a datetime.
|||

Try using:

Code Snippet

="SELECT * FROM dbo.v_call

WHERE (dbo.v_call.entry_date >= DATEADD(d," &

CSTR(@.period) &

", GETDATE())) "

>L<

|||

For what it is worth, I have used the following with no problems

@.Trend is an integer parameter of 30, 90, 180, 365 or 720.

Code Snippet

authopen_idx >= dateadd(d,@.Trend*-1, getdate())

Can't use integer parameter with dateadd?

Hey guys I have the following in my SQL statement:

DATEADD(hh, @.hours, @.startTime)

hours is an integer and startTime is actually a string, but the thing is that this statement works fine if I use it like this:

DATEADD(hh, 8, @.startTime)

The first version is what I need so that the user can control from a parameter and I get the following error:

Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.

Thanks!

BJ

have you tried to convert the variable?

Code Snippet

DATEADD(hh, convert(int,(@.hours)), @.startTime)

Simone

Can't use integer parameter with dateadd?

Hey guys I have the following in my SQL statement:
DATEADD(hh, @.hours, @.startTime)
hours is an integer and startTime is actually a string, but the thing is
that this statement works fine if I use it like this:
DATEADD(hh, 8, @.startTime)
The first version is what I need so that the user can control from a
parameter and I get the following error:
Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.
Thanks!
BJOn May 11, 1:50 pm, bjkaledas <bjkale...@.discussions.microsoft.com>
wrote:
> Hey guys I have the following in my SQL statement:
> DATEADD(hh, @.hours, @.startTime)
> hours is an integer and startTime is actually a string, but the thing is
> that this statement works fine if I use it like this:
> DATEADD(hh, 8, @.startTime)
> The first version is what I need so that the user can control from a
> parameter and I get the following error:
> Error Source: System.Data
> Error Message: Failed to convert parameter value from a Decimal to a DateTime.
> Thanks!
> BJ
You might need to do something like this:
DATEADD(hh, @.hours, CAST(@.startTime AS DATETIME))
- or- something like this in the report:
=Dateadd("h", Parameters!Hours.Value, CDate(Fields!startTime.Value))
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant