Building a statement from parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Building a statement from parameters

Author

Topic

dpcolgan
Starting Member

4 Posts

Posted - 2007-05-08 : 11:15:56

Hi, this should be simple so I must be spacing out. Been a year since I worked seriously with SQL Server..

I'm writing a SP and am passing in a parameter which is the "AND" clause. The tricky part here is that I'm trying to do a "Select into and I can't get the syntax right...It wants to try to convert the variables to int for some reason

Here is my proc...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: Dan Colgan
-- Create date: 5/7/07
-- Description: Replace Table Create for NAS Report
-- =============================================

-- exec usp_CreateNasReportInserts_New "and (groop.catgid = (64))"
ALTER PROCEDURE [dbo].[usp_CreateNasReportInserts_New] (
-- Add the parameters for the stored procedure here
@andclause varchar(8000) = null
)

-- ***********************************************************
-- CURRENT SELECT STATEMENT THAT RETURNS A RESULTSET
-- TO ASP IN BUILDFILE.ASP
-- NOTES: DC 5/7/07 - ALTERING THIS TO DO A BULK INSERT
-- HERE INSTEAD OF LOOPING ONE RECORD AT A TIME IN ASP
-- ***********************************************************

-- *******************************************************************************
-- STEP ONE FROM ASP PAGE
-- *******************************************************************************
--....IS TO DELETE ANY BUILDREPORT RECORDS
-- THAT HAVE A REPORT DATE THAT DOES * NOT * = "Date" (today)

as

begin

DECLARE @TODAYSDATE Datetime

SET @TODAYSDATE = GETDATE()

--ACTUAL DELETE STATEMENT FROM BUILDFILE.ASP
--delete from BuildReport Where ReportDate <> '" & date & "'"
--REDONE AS ...
DELETE FROM BUILDREPORT WHERE REPORTDATE <> @TODAYSDATE

--*******************************************************************************
-- STEP TWO (2) FROM ASP PAGE IS TO GET THE NEXT REPORTID
-- strSQL = "select max(ReportID) as ReportIDMax from BuildReport"
-- *******************************************************************************
DECLARE @NEWID integer
Set @NEWID = (select max(ReportID) from BuildReport)

If @NewID is not null
set @NewID = @NewID + 1
Else
set @NewID = 1

--select @NewID
--IF ReportID = "" or Isnull(ReportID) then ReportID = 1

-- ********************************************************************************
-- STEP THREE (3) FROM ASP PAGE IS TO FILL A RECORDSET WITH
-- THE RECORDS IT FINDS... NOT GOING TO DO THAT INSTEAD
-- I WILL WRITE THEM TO THE BUILDREPORT TABLE DIRECTLY
-- ********************************************************************************
Declare @MySQL varchar(8000)

IF @andclause is not null
set @MySQL = 'SELECT @NEWID as ReportID, getdate() as ReportDate,name.nameid,NAME.LASTNAME, NAME.FIRSTNAME, NAME.MIDNAME, NAME.LNAMESUF, NAME.TITLE, COUNTY.COUNTY, NAME.MGDSTRCT, ADDRESS.ADDRESS1, ADDRESS.ADDRESS2, ADDRESS.ADDRESS3, ADDRESS.ADDRESS4, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.ZIPCODE, NAME.SALUTATION, NAME.FRMOFADD, CATEGORY.CTG_NAME,Address.EMAIL,NAME.COMNT,NAME.COMNT2,Address.Phone,Address.PhoneExt,Address.Fax,Address.FaxComnt FROM ((NAME INNER JOIN ADDRESS ON NAME.NAMEID = ADDRESS.NAMEID) INNER JOIN (GROOP INNER JOIN CATEGORY ON GROOP.CATGID = CATEGORY.CATGID) ON (GROOP.ADDRID = ADDRESS.ADDRID) AND (NAME.NAMEID = GROOP.NAMEID)) INNER JOIN COUNTY ON NAME.COUNTY = COUNTY.CNTYCODE WHERE Name.NameID >= 1 ' + @andClause
else

set @MySQL = 'SELECT @NewID as ReportID,getdate() as ReportDate,name.nameid,NAME.LASTNAME, NAME.FIRSTNAME, NAME.MIDNAME, NAME.LNAMESUF, NAME.TITLE, COUNTY.COUNTY, NAME.MGDSTRCT, ADDRESS.ADDRESS1, ADDRESS.ADDRESS2, ADDRESS.ADDRESS3, ADDRESS.ADDRESS4, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.ZIPCODE, NAME.SALUTATION, NAME.FRMOFADD, CATEGORY.CTG_NAME,Address.EMAIL,NAME.COMNT,NAME.COMNT2,Address.Phone,Address.PhoneExt,Address.Fax,Address.FaxComnt FROM ((NAME INNER JOIN ADDRESS ON NAME.NAMEID = ADDRESS.NAMEID) INNER JOIN (GROOP INNER JOIN CATEGORY ON GROOP.CATGID = CATEGORY.CATGID) ON (GROOP.ADDRID = ADDRESS.ADDRID) AND (NAME.NAMEID = GROOP.NAMEID)) INNER JOIN COUNTY ON NAME.COUNTY = COUNTY.CNTYCODE WHERE Name.NameID >= 1'
end

--exec(@MySQL)
select @MySQL

dpcolgan
Starting Member

4 Posts

Posted - 2007-05-08 : 11:20:15

I re-read the posting and it appears a bit more explanation might help.

Another developer had written an asp page which first went out and got the records pulled back to an adodb.recordset and then looped through the recordset (sometimes 1000's of records) to insert one at a time into this buildReport table.

I'd like to do that with one stored proc (seems alot more sensible and efficient). I am not able to formulate the statement which needs to insert the new RecordID and the Report Date as well as the optional AND Clause....

dpcolgan
Starting Member

4 Posts

Posted - 2007-05-08 : 11:21:12

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources