How to check if park_id already exist in table calendar by stored procedure?

stored procedure

ALTER PROCEDURE [dbo].[KalenderVullen]
@park_id numeric,
@startdate Datetime 

AS
BEGIN

;WITH Dates AS (
        SELECT DATEADD(day,DATEDIFF(day,0,@startdate),0) as DateOf,
            CASE WHEN datename(weekday,@startdate) IN ('Saturday','Sunday') THEN 'Weekend'
                 ELSE 'Week' 
            END DayType,
            datename(weekday,@startdate) DayOfWeekName
        UNION ALL 
        SELECT DateOf+1,
            CASE WHEN datename(weekday,DateOf+1) IN ('Saturday','Sunday') THEN 'Weekend'
                 ELSE 'Week' 
            END DayType,
            datename(weekday,DateOf+1) DayOfWeekName
        FROM Dates
        where DateOf < dateadd(yy, 10, @startdate)


)

INSERT INTO  kalender (park_id,datum,week_weekend,naam_dag)
   SELECT @park_id,DateOf,DayType,DayOfWeekName
  开发者_运维问答  FROM Dates
    OPTION (MAXRECURSION 4000)
    SELECT @@IDENTITY AS [@@IDENTITY]
    print @@IDENTITY
    set @park_id = @@IDENTITY




END

code in VB.NET (DAL)

Public Sub kalender_insert(ByVal i_startdatum As Date, ByVal i_park_id As Int16)

    Dim nwindConn As SqlConnection = New SqlConnection(conStr)
    Dim adocmd As SqlCommand
    Dim adopara As New SqlParameter

    Dim mytrans As SqlTransaction

    nwindConn.Open()

    'begin van commitstructuur
    '‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
    mytrans = nwindConn.BeginTransaction(IsolationLevel.ReadCommitted)

    adocmd = New SqlCommand("KalenderVullen", nwindConn)
    adocmd.CommandType = CommandType.StoredProcedure

    adocmd.Parameters.Add(New SqlParameter("@startdate", SqlDbType.DateTime, 50)).Value = i_startdatum
    adocmd.Parameters("@startdate").Direction = ParameterDirection.Input

    adocmd.Parameters.Add(New SqlParameter("@park_id", SqlDbType.Int, 50)).Value = i_park_id
    adocmd.Parameters("@park_id").Direction = ParameterDirection.Input

    '‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
    adocmd.Transaction = mytrans
    'het committen wordt afhankelijk gemaakt van het slagen
    '‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
    Try
        adocmd.ExecuteNonQuery()
        mytrans.Commit()
    Catch ex As Exception
        mytrans.Rollback()
    End Try



    mytrans.Dispose()
    mytrans = Nothing
    adocmd.Dispose()
    adocmd = Nothing
    nwindConn.Close()
    nwindConn.Dispose()
    nwindConn = Nothing
    MessageBox.Show("kalender is aangemaakt")
End Sub

code in BLL

????????????

Why assign @park_id with the new value if you've aent it in as a parameter?

And always use SCOPE_IDENTITY

Guessing, try this in the SQL

INSERT INTO  kalender (park_id,datum,week_weekend,naam_dag)
SELECT @park_id,DateOf,DayType,DayOfWeekName
FROM Dates
WHERE NOT EXISTS (SELECT * FROM Dates WHERE park_id = @park_id
OPTION (MAXRECURSION 4000);

SELECT @park_id = SCOPE_IDENTITY();

I am not sure If I understand correctly, but maybe you are looking for:

IF EXISTS (SELECT TOP 1 1 FROM kalender WHERE park_id = @park_id) BEGIN
--- here comes you code
END

SELECT TOP 1 1 is efficient form of checking for record existent with given id (assuming that there is a index on park_id column).

@gbn pointed out in comments that TOP 1 1 is not needed. Here are my arguments why they are useful:

1. Inner query could return a lot of records (depends on query conditions). This could lead to terrible query execution plan -- I see that from time to time on MS SQL 2005.
2. It matters how much data I read from disk. Using * instead of constant could lead to terrible query plan too. Especially if DB thinks there are a lot of records to return.

Of course if I query by primary key column (or unique column) there should be no more than 1 record. In many other cases -- I would not count on query optimalizer, so I think it's much better to say exactly what I need (in every case). I use TOP 1 1 with EXISTS as idiom.