Using results multiple times
-
OK, probably it is an easy answer, but I just can't get it working. I'm working on SQL SERVER 2005. Here is stored procedure:
SELECT TOP (50) * FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN) SELECT * FROM h_Encounter WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN)) SELECT cn.*, u.Username AS Username FROM h_CaseNote cn INNER JOIN c_User u ON u.UserId = cn.UserId WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN)) SELECT * FROM h_CasePDR WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN)) RETURNSo it is obvious what I'm trying to do? I just want to reuse selected cases. I tried with:WITH C AS ( SELECT TOP (50) CaseId FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN) SELECT * FROM C SELECT * FROM h_Encounter WHERE CaseId IN (SELECT CaseId FROM C) SELECT cn.*, u.Username AS Username FROM h_CaseNote cn INNER JOIN c_User u ON u.UserId = cn.UserId WHERE CaseId IN (SELECT CaseId FROM C) SELECT * FROM h_CasePDR WHERE CaseId IN (SELEC -
OK, probably it is an easy answer, but I just can't get it working. I'm working on SQL SERVER 2005. Here is stored procedure:
SELECT TOP (50) * FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN) SELECT * FROM h_Encounter WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN)) SELECT cn.*, u.Username AS Username FROM h_CaseNote cn INNER JOIN c_User u ON u.UserId = cn.UserId WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN)) SELECT * FROM h_CasePDR WHERE CaseId IN (SELECT TOP (50) CaseId FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN)) RETURNSo it is obvious what I'm trying to do? I just want to reuse selected cases. I tried with:WITH C AS ( SELECT TOP (50) CaseId FROM h_Case WHERE ((FirstName LIKE @FirstName) AND (LastName LIKE @LastName) OR ((IsNull(Aliases, '') LIKE @FirstName) AND (IsNull(Aliases, '') LIKE @LastName))) AND (DOB BETWEEN @DobFrom AND @DobTo) AND (IsNull(GenderId, -1) BETWEEN @GenderFrom AND @GenderTo) AND (IsNull(PHN, '') LIKE @PHN) SELECT * FROM C SELECT * FROM h_Encounter WHERE CaseId IN (SELECT CaseId FROM C) SELECT cn.*, u.Username AS Username FROM h_CaseNote cn INNER JOIN c_User u ON u.UserId = cn.UserId WHERE CaseId IN (SELECT CaseId FROM C) SELECT * FROM h_CasePDR WHERE CaseId IN (SELECThe temporary table is most likely your best resort. The table will sit in memory waiting till the end of the procedure to be used. That being said, if you want to be lazy... You could do what I call a "String Procedure" (instead of a stored procedure). Remember that this method is very vulnerable to SQL injection/truncation attacks DECLARE @FilterSQL AS varchar(8000) SET @SQL = 'SELECT TOP (50) * FROM h_Case WHERE ((FirstName LIKE ''' + @FirstName + ''') AND (LastName LIKE ''' + @LastName + ''') OR ((IsNull(Aliases, '''') LIKE ''' + @FirstName + ''' AND (IsNull(Aliases, '''') LIKE ''' + @LastName + '''))) AND (DOB BETWEEN + ''' @DobFrom + '''AND ''' + @DobTo + ''') AND (IsNull(GenderId, -1) BETWEEN ' + @GenderFrom + ' AND ' + @GenderTo + ') AND (IsNull(PHN, '''') LIKE ''' + @PHN + ''')' Then insert the @SQL variable into your other SQL. So use the temporary table. Hogan
-
The temporary table is most likely your best resort. The table will sit in memory waiting till the end of the procedure to be used. That being said, if you want to be lazy... You could do what I call a "String Procedure" (instead of a stored procedure). Remember that this method is very vulnerable to SQL injection/truncation attacks DECLARE @FilterSQL AS varchar(8000) SET @SQL = 'SELECT TOP (50) * FROM h_Case WHERE ((FirstName LIKE ''' + @FirstName + ''') AND (LastName LIKE ''' + @LastName + ''') OR ((IsNull(Aliases, '''') LIKE ''' + @FirstName + ''' AND (IsNull(Aliases, '''') LIKE ''' + @LastName + '''))) AND (DOB BETWEEN + ''' @DobFrom + '''AND ''' + @DobTo + ''') AND (IsNull(GenderId, -1) BETWEEN ' + @GenderFrom + ' AND ' + @GenderTo + ') AND (IsNull(PHN, '''') LIKE ''' + @PHN + ''')' Then insert the @SQL variable into your other SQL. So use the temporary table. Hogan
Would it be smarter to use Table variables in this case? I read that they interrupt parallel execution. Thanks for your previous answer in any case!
-
Would it be smarter to use Table variables in this case? I read that they interrupt parallel execution. Thanks for your previous answer in any case!
-
Tnx for your answers!