Queries with param

  • Thread starter Thread starter Johnny Emde
  • Start date Start date
J

Johnny Emde

Hey Group

Will this be possible to convert into Access (mdb) Query?

CREATE PROCEDURE sp_TeamAdd
@p_TeamName nvarchar(50),
@p_Notes text,
@p_TeamID_out bigint output,
@p_LeaderName nvarchar(10)
AS

-- Teamname can only exists once in the table
IF NOT EXISTS ( SELECT * FROM [tblTeams] WHERE [TeamName] = @p_TeamName)

BEGIN

DECLARE @v_Created datetime
DECLARE @v_Modified datetime
DECLARE @v_LeaderID bigint

SET @v_Created = getdate()
SET @v_Modified = @v_Created

INSERT INTO [tblTeams]
( [TeamName],
[TeamNotes],
[Created],
[Modified]
)
Values
(
@p_TeamName,
@p_Notes,
@v_Created,
@v_Modified
)

SET @p_TeamID_out = SCOPE_IDENTITY()

IF @p_LeaderName IS NOT NULL
SET @v_LeaderID = ( SELECT TOP 1 [UserID] FROM [tblUsers] WHERE
[UserIdent] = @p_LeaderName )

IF @v_LeaderID IS NOT NULL
UPDATE [tblTeams] SET [FK_TeamLeader] = @v_LeaderID WHERE [TeamID] =
@p_TeamID_out
ELSE
RAISERROR ( 'No Leaders was found.', 16, 1 )

END

Kind regards
Johnny Emde Jensen
 
Johnny Emde said:
Hey Group

Will this be possible to convert into Access (mdb) Query?
Click to expand...

Kind regards
Johnny Emde Jensen
Click to expand...

Johnny Emde,

I was in the middle of translating the SQL Server stored procedure
into a VBA function, when I noticed that the INSERT statement did
not contain the FK_TeamLeader column.

"FK" implies a foreign key, and indeed, I created my test tables for
the previous question with that in mind.

An INSERT cannot be done if there is no value supplied for a fk
column (I suppose there could be a default value, but that wouldn't
seem right in this case). I'm wondering how this proc works in SQL
Server. Is the column just prefixed with "FK_", but has no foreign
key constraint?

Also, a TeamID column has poppped up for tblTeams

Can you please post your DDL (from SQL Server)?
(www.aspfaq.com/5006 will show you how to get it if you don't have
it already.)


Sincerely,

Chris O.
 
Back
Top