"jonefer" <jone***@discussions.microsoft.com> wrote in message
news:368A30C8-8EBE-4E77-873F-4378A4DC1905@microsoft.com...
> When I try to compile this (complete Store Procedure below)
> I get the following error:
> The select list for the INSERT statement contains fewer items than the
> insert list.
>
> I know what is causing this to bomb is the complex Grouping (which I've
> marked below as ---COMPLEX GrOUPING
>
> (this grouping has been tested and works and MUST be used - but won't work
> within the constructs of this stored procedure)  can someone help me  with
> the details  of getting it to work?  Thanks so much.
>
>
> CREATE PROCEDURE qMemberSelect_paging
> @MRN int = NULL,
> @MemNAME nvarchar(40) = NULL,
> @DOB datetime = NULL,
> @SSN nvarchar(9) = NULL,
> @SEX nvarchar(1) = NULL,
> @StartRow int = NULL,
> @StopRow int = NULL,
> @debug bit = 0 AS
>
> ---- Build a table variable with an IDENTITY column
>
> DECLARE
> @sql nvarchar (4000)
> DECLARE
> @t_table table(
> [rownum] [int] IDENTITY (1,1) Primary key NOT NULL,
> [MRN] [int],
> [MemNAME] [nvarchar] (40),
> [SSN] [nvarchar] (9),
> [DOB] [datetime],
> [SEX] [nvarchar] (1),
> [HML] [nvarchar] (3),
> [PHN] [nvarchar] (7),
> [GROUP][nvarchar] (5),
> [SGR] [nvarchar] (3),
> [REL] [nvarchar] (2),
> [PROVIDER] [nvarchar] (4),
> [PCL] [nvarchar] (3),
> [EXP] [nvarchar] (1),
> [FROM-DT] [datetime],
> [THRU-DT] [datetime])
>
>
>
>
> SET @sql =
> 'SELECT MRN,
> MemNAME,
> SSN,
> DOB,
> SEX,
> HML,
> PHN,
> [GROUP],
> SGR,
> REL,
> PROVIDER,
> PCL,
> [EXP],
> [FROM-DT],
> [THRU-DT]
> FROM dbo.MEMBERSHIP AS Y
> WHERE 1=1'
>
>
> if @MRN IS NOT NULL
> SELECT @sql = @sql + ' AND MRN = @MRN'
>
> if @MemNAME IS NOT NULL
> SELECT @sql = @sql + ' AND MemName like @MemNAME + ''%'''
>
> if @DOB IS NOT NULL
> SELECT @sql = @sql + ' AND DOB = @DOB'
>
> if @SSN IS NOT NULL
> SELECT @sql = @sql + ' AND SSN = @SSN'
>
> if @SEX IS NOT NULL
> SELECT @sql = @sql + ' AND SEX = @SEX'
>
> if @debug = 1
> PRINT @sql
>
> ----Limit upper bound to the StopRow parameter
> SET RowCount @StopRow
>
> SET NOCOUNT ON
>
>
> ---COMPLEX GrOUPING
> SET @sql = @sql + ' GROUP BY MRN, MemNAME, SSN, DOB, SEX, HML, PHN,
> [GROUP], SGR, REL, PROVIDER, PCL, [EXP], [FROM-DT], [THRU-DT]
> HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
> Membership As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
> From Membership  As Z Where Z.[THRU-DT] Is Null))
> ORDER BY MemNAME, [FROM-DT] DESC'
>
> --Fill in the table variable
> insert @t_table
> (MRN,
> MemNAME,
> SSN,
> DOB,
> SEX,
> HML,
> PHN,
> [GROUP],
> SGR,
> REL,
> PROVIDER,
> PCL,
> [EXP],
> [FROM-DT],
> [THRU-DT])
>
> SELECT @sql
>
>
>  --return the proper rows to the application
> SELECT * FROM @t_table WHERE rownum >=@StartRow
> ORDER BY rownum
>
> GO

"David Portas" wrote:

> "jonefer" <jone***@discussions.microsoft.com> wrote in message
> news:368A30C8-8EBE-4E77-873F-4378A4DC1905@microsoft.com...
> > When I try to compile this (complete Store Procedure below)
> > I get the following error:
> > The select list for the INSERT statement contains fewer items than the
> > insert list.
> >
> > I know what is causing this to bomb is the complex Grouping (which I've
> > marked below as ---COMPLEX GrOUPING
> >
> > (this grouping has been tested and works and MUST be used - but won't work
> > within the constructs of this stored procedure)  can someone help me  with
> > the details  of getting it to work?  Thanks so much.
> >
> >
> > CREATE PROCEDURE qMemberSelect_paging
> > @MRN int = NULL,
> > @MemNAME nvarchar(40) = NULL,
> > @DOB datetime = NULL,
> > @SSN nvarchar(9) = NULL,
> > @SEX nvarchar(1) = NULL,
> > @StartRow int = NULL,
> > @StopRow int = NULL,
> > @debug bit = 0 AS
> >
> > ---- Build a table variable with an IDENTITY column
> >
> > DECLARE
> > @sql nvarchar (4000)
> > DECLARE
> > @t_table table(
> > [rownum] [int] IDENTITY (1,1) Primary key NOT NULL,
> > [MRN] [int],
> > [MemNAME] [nvarchar] (40),
> > [SSN] [nvarchar] (9),
> > [DOB] [datetime],
> > [SEX] [nvarchar] (1),
> > [HML] [nvarchar] (3),
> > [PHN] [nvarchar] (7),
> > [GROUP][nvarchar] (5),
> > [SGR] [nvarchar] (3),
> > [REL] [nvarchar] (2),
> > [PROVIDER] [nvarchar] (4),
> > [PCL] [nvarchar] (3),
> > [EXP] [nvarchar] (1),
> > [FROM-DT] [datetime],
> > [THRU-DT] [datetime])
> >
> >
> >
> >
> > SET @sql =
> > 'SELECT MRN,
> > MemNAME,
> > SSN,
> > DOB,
> > SEX,
> > HML,
> > PHN,
> > [GROUP],
> > SGR,
> > REL,
> > PROVIDER,
> > PCL,
> > [EXP],
> > [FROM-DT],
> > [THRU-DT]
> > FROM dbo.MEMBERSHIP AS Y
> > WHERE 1=1'
> >
> >
> > if @MRN IS NOT NULL
> > SELECT @sql = @sql + ' AND MRN = @MRN'
> >
> > if @MemNAME IS NOT NULL
> > SELECT @sql = @sql + ' AND MemName like @MemNAME + ''%'''
> >
> > if @DOB IS NOT NULL
> > SELECT @sql = @sql + ' AND DOB = @DOB'
> >
> > if @SSN IS NOT NULL
> > SELECT @sql = @sql + ' AND SSN = @SSN'
> >
> > if @SEX IS NOT NULL
> > SELECT @sql = @sql + ' AND SEX = @SEX'
> >
> > if @debug = 1
> > PRINT @sql
> >
> > ----Limit upper bound to the StopRow parameter
> > SET RowCount @StopRow
> >
> > SET NOCOUNT ON
> >
> >
> > ---COMPLEX GrOUPING
> > SET @sql = @sql + ' GROUP BY MRN, MemNAME, SSN, DOB, SEX, HML, PHN,
> > [GROUP], SGR, REL, PROVIDER, PCL, [EXP], [FROM-DT], [THRU-DT]
> > HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
> > Membership As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
> > From Membership  As Z Where Z.[THRU-DT] Is Null))
> > ORDER BY MemNAME, [FROM-DT] DESC'
> >
> > --Fill in the table variable
> > insert @t_table
> > (MRN,
> > MemNAME,
> > SSN,
> > DOB,
> > SEX,
> > HML,
> > PHN,
> > [GROUP],
> > SGR,
> > REL,
> > PROVIDER,
> > PCL,
> > [EXP],
> > [FROM-DT],
> > [THRU-DT])
> >
> > SELECT @sql
> >
> >
> >  --return the proper rows to the application
> > SELECT * FROM @t_table WHERE rownum >=@StartRow
> > ORDER BY rownum
> >
> > GO
>
> The final line of your proc should be EXEC (@sql) instead of SELECT. That
> won't work in SQL Server 2000 however. Only 2005 will permit INSERT... EXEC
> into a variable.
>
> For working paging solutions see:
> http://www.aspfaq.com/show.asp?id=2120
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>

"jonefer" <jone***@discussions.microsoft.com> wrote in message
news:53E813E1-02E2-430D-9DF1-FCF3D8D58ECF@microsoft.com...
> Since I'm the poor developer that doesn't have SQL 2005, what options do I
> have to get this idea to work - ...
>
> I created a view called vMemberResults to do the grouping
> so that I could do a non-variable select  - but I still need to do that
> special parameter processing:
>
>    if @MRN IS NOT NULL
>            SELECT @sql = @sql + ' AND MRN = @MRN'
>
>    if @MemNAME IS NOT NULL
>             SELECT @sql = @sql + ' AND MemName like @MemNAME + ''%'''
> ...etc.
>
> I somehow need to get that result set to a variable temp table.
>
>