Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

MS SQL Server 2005 - Creating a query

Hi and thanks,

I have the following code:

DECLARE @sql nvarchar(4000)
DECLARE @Valid1 nvarchar(4000)
DECLARE @Valid2 nvarchar(4000)

DECLARE main_cursor CURSOR FOR
SELECT Valid1 FROM dbo.TEMP_PWC_ValTable
Where CAST(Valnum as varchar(10)) in( '1' , '43', '44', '46')

OPEN main_cursor
SET @sql = ''
SET @Valid1 = ''
SET @Valid2 = ''
-- Perform the first fetch.
FETCH NEXT FROM main_cursor
INTO @Valid1


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @sql = ''
      BEGIN
            IF @Valid2 = ''
                  SET @sql = 'select * from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1
            ELSE
                  SET @sql = 'select * from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1 + ' and ' + @Valid2
      END
      ELSE
      BEGIN
            IF @Valid2 = ''
                  SET @sql = @sql +' union ' + 'select * from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1
            ELSE
                  SET @sql = @sql +' union ''select * from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1 + ' and ' + @Valid2
      END
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM main_cursor
      INTO @Valid1
     
     
END


CLOSE main_cursor
DEALLOCATE main_cursor

--print  @sql
EXEC sp_executesql @sql


I need help on what this code is doing?

Where do I put(submit) the query code?(exp - when creating a Table one would put the creation under the Tables folder or when creating a Store Procedure one would put that under Programmability-Stored Procedures folder)

On this line:
Where CAST(Valnum as varchar(10)) in( '1' , '43', '44', '46')

1.) Do I have to make sure that Valnum is declared as varchar? (I have this declared as Text)?
2.) How do I create a table and put the results into that Table?

Thanks
Avatar of Simone B
Simone B
Flag of Canada image

"I need help on what this code is doing?"
What are the values of the variables @Valid1 and @Valid2.

@Valid1 is populated in the cursor, and you have it in a where clause in your @sql. That requires it to be a comparison of some kind. So @Valid1 should look something like: 'WHERE Field = value' If @Valid1 is a value only, this will throw errors when run.

@Valid2 appears to never be populated with any value, and remains an empty string.

"Where do I put(submit) the query code?"
I would normally use a cursor within a stored procedure.

"Do I have to make sure that Valnum is declared as varchar?"
That would certainly be better than text. But it appears that it's an int. ??

You are executing the @sql outside of the cursor. That means it will only execute for the last value of @Valid1. If you want it to execute for each value of @Valid1, your sp_execute would have to be within the cursor.
Avatar of Amour22015
Amour22015

ASKER

Hi,

This is how new I am to SQL..
My question:
"Where do I put(submit) the query code?"

I found the New Query at the top left side of SSMS.

What this query does is in the Table ValTable I have Vaild1 and Valid2 both are Functions and that is why they are being placed inside of a string? So what the table ValTable is "FUNCTIONS"

My question:
"Do I have to make sure that Valnum is declared as varchar?"

The reason for this is that It comes from a Excel Spreadsheet,
When I tryed to use varchar (when creating the Table ValTable) I received error and it would not allow me to copy and paste into the Table
Is this the correct way to do this?

Thanks
SELECT Valid1 FROM dbo.TEMP_PWC_ValTable
Where CAST(Valnum as varchar(10)) in( '1' , '43', '44', '46')

If you paste the code above into a "New Query" and click Execute, what results do you get, if any?
Hi and thanks,

See attachment:

So Valid1 & 2 are functions

Thanks
ValCode.docx
Hi,

What I am trying to do is that if the function condition is true then add 1 to a counter giving the user all the possible errors that are in a Table.

So each record consist of a error checking so that the user can go back into each record and correct the information.

What VALTABle is:

Someone enters in one or two functions to check if there are any possible errors in the current database.

Thanks
Perfect, thanks.

So, you open your cursor, then assign empty strings to all 3 of your variables. Your first fetch populates @Valid1 with the first row from your select. @sql and @Valid2 are still empty strings. So the following applies on the first pass of the cursor:

      IF @sql = ''
      BEGIN
            IF @Valid2 = ''
                  SET @sql = 'select * from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1

You have now populated the variable @sql.

You then do another fetch, which populates @Valid1 with the second row from your select. Now this applies:

            IF @Valid2 = ''
                  SET @sql = @sql +' union ' + 'select * from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1

The same will apply for each subsequent pass of the cursor, and you will end up with @sql containing as many select statements as there are rows in the cursor, with a union between each. Then  your @sql is executed. That will work, but @Valid2 will never be populated. If that's what you're looking for, then you really don't need @Valid2 as it's always empty.

In order to test the results of this code, you can execute it directly in Management Studio. That will show you the results of executing @sql. I recommend you do this before writing to a table, just to make sure you are getting what you need.
I just read your last post. I don't see a counter in your cursor.
Hi,

I don't think I am going to use the counter until after the Table is created.

What I am doing is using Crystal Reports 2008 so it will read the newly created Table and count on what the table produces.

The ValTable has the following fields:

Active
Valnum
FileName
Valid1
Valid2
Field1
Field2

Valid1 & Valid2 contain Functions to check conditions agaisnt the Main Table

Thanks
When you execute the code from your original post, do you get the results you need for insertion into the new table?
Hi,

I get results (but I don't know if the results are correct)  and I can place into a table (but I have to know how)

Thanks
Hi,

Ok, I just check one condition by doing this:
Where CAST(Valnum as varchar(10)) = '1'

That should have given me:

Valid1 = If TaxType = 3
And
Valid2 = If Usecode <> 092

But it only gave me:
Valid1 = If Taxtype = 3

Valid2 did not work.

So what is wrong with the code?

Please help and thanks
Hi,

should I be doing this:
SELECT Valid1, Valid2 FROM dbo.TEMP_PWC_ValTable

Thanks
Hi,


But then I receive an error:
Msg 16924, Level 16, State 1, Line 15
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.


Please help and thanks
Hi,

So I found that problem and the 2 Valids are now working.

The only thing left is how to create a table out of what I did?

Thanks
Hi,

You mentioned that I did not have a counter in the cursor?

How do you do this and how do I add it to the newly created Table?

Thanks
Hi,

The count has to be based on each set of functions like:

when I tested the first occurance:
Where CAST(Valnum as varchar(10)) = '1'

I received 67 records that had the problems

So the counter would have to have 67 problems

Then the next check would have a seperate count.

And I would have to put this count into the newly created Table.
I would also have to ground that count that is in the new table.

So maybe have the Valnum come over also to the newly created Table so I can group the counts.

So I would have to have 2 more fields added to the new table.
Field1 = count
Field2 = Valnum (in this case Valnum = 1)

Thanks
My apologies, I am in meetings for the next couple of hours. You will need to create a table with all of the columns and data types you need:

CREATE TABLE TableName
(Column1 datatype1,
Column2 datatype2,
...)

I will do a bit of research as to the best way to populate the table.

In the meantime, you will also need to put your existing code into a stored procedure:

CREATE PROCEDURE ProcedureName
AS BEGIN

your code .....

END

...back in a couple of hours ...
Hi,

I don't need the count option, because I can just use the Valnum to do the same.

I do need (when creating the Table) to include the Valnum.

Also need to have the stored procedure drop/create the Table.

Table is going to be a temp table.

Please help and thanks
If you're going to use a temp table, then your create table statement is the same, just add a hashmark before the table name:

Create table #TableName

The temp table will exist only while the procedure is running. If you want to use a "normal" table, then you will drop and recreate it within the procedure, but outside of the cursor:

IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))
DROP TABLE TableName

CREATE TABLE TableName
etc.....

Once you've dropped and recreated the table, you should be able to populate it like this:

INSERT TableName EXEC sp_executesql @sql
Hi,

I also want to add the field (from ValTable.Valnum) to the new Table?

Thanks
Then you will have to add it to your select statement in @sql, as welll as adding the column in your new table.

I don't know if the two tables are able to be joined, or if so what kind of join is needed, or what the key fields are, but the basic syntax is like this:

select A.*, B.Valnum
from TEMP_PWC_Report_SP_MAIN as A
join TEMP_PWC_ValTable as B
on A.KeyField = B.Keyfield
etc...
Hi,

don't think I can join (here is no match between the 2 Tables).

Have noticed under conditions like:

If Valnum = 1 then:
I will receive 67 records.
that means that there where 67 records (from the Main Table) that matched the formula conditions and each formula condition has a unqu .

Thanks
If you can't join the tables, then you will have to return Valnum in your cursor, and add the associated variable to your select in @sql.

So all of your select statements would start something like this:

select *, @Valnum from [TEMP_PWC_Report_SP_MAIN] .........
Hi,

Ok, this is what I have so far:
DECLARE @sql nvarchar(4000)
DECLARE @Valnum nvarchar(4000)
DECLARE @Valid1 nvarchar(4000)
DECLARE @Valid2 nvarchar(4000)

DECLARE main_cursor CURSOR FOR
SELECT Valid1, Valid2, Valnum FROM dbo.TEMP_PWC_ValTable
Where CAST(Active as varchar(10)) = 'TRUE' And CAST(FileName as varchar(10)) = 'MAIN'
--Where CAST(Valnum as varchar(10)) = '1'

OPEN main_cursor
SET @sql = ''
SET @Valnum = ''
SET @Valid1 = ''
SET @Valid2 = ''
-- Perform the first fetch.
FETCH NEXT FROM main_cursor
INTO @Valid1, @Valid2, @Valnum


-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @sql = ''
      BEGIN

            IF @Valid2 = ''
                  --SET @sql = 'select mpropertynumber from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1
                  SET @sql = 'select mpropertynumber, @Valnum from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1
            ELSE
                  --SET @sql = 'select mpropertynumber from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1 + ' and ' + @Valid2
                  SET @sql = 'select mpropertynumber, @Valnum from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1 + ' and ' + @Valid2
      END
      ELSE
      BEGIN
            IF @Valid2 = ''
                  --SET @sql = @sql +' union ' + 'select mpropertynumber from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1
                          SET @sql = @sql +' union ' + 'select mpropertynumber, @Valnum from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1
            ELSE
                  --SET @sql = @sql +' union ' + 'select mpropertynumber from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1 + ' and ' + @Valid2
                    SET @sql = @sql +' union ' + 'select mpropertynumber, @Valnum from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1 + ' and ' + @Valid2
      END
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM main_cursor
      INTO @Valid1, @Valid2, @Valnum
     
     
END


IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TEMP_PWC_VALTEMP]') AND type in (N'U'))
DROP TABLE [dbo].[TEMP_PWC_VALTEMP]

--SELECT mpropertynumber
--INTO TEMP_PWC_VALTEMP
--FROM TEMP_PWC_Report_SP_MAIN

CREATE TABLE [dbo].[TEMP_PWC_VALTEMP]
(
 mpropertynumber varchar(255),
 Valnum varchar(255),
 )

CLOSE main_cursor
DEALLOCATE main_cursor

--print  @sql
EXEC sp_executesql @sql

But I keep getting errors:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Valnum"

What am I doing wrong?

Please help
ASKER CERTIFIED SOLUTION
Avatar of Simone B
Simone B
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

Looks alot closer.

But for some reason I am getting double the amount in the Table

Notice when I do just Valnum = 1 I get 67 records yet when I direct it to the table:
INSERT TEMP_PWC_VALTEMP EXEC sp_executesql @sql

I get 134 records

help please
That will be difficult without seeing the data. But I'll give it a shot.

This is what populates your cursor:
SELECT Valid1, Valid2, Valnum FROM dbo.TEMP_PWC_ValTable
Where CAST(Active as varchar(10)) = 'TRUE' And CAST(FileName as varchar(10)) = 'MAIN'

Run that and see what your first row is. Those will be the variables used to populate @sql. Use those values in your @sql to see what you get.
'select mpropertynumber, ' + @Valnum + ' from [TEMP_PWC_Report_SP_MAIN] where ' + @Valid1 + ' and ' + @Valid2

Take the second row from your cursor, and do the same, etc. You will be basically walking through all of the steps 'manually' to see what you can expect.

This will help you determine where the problem is. It could be as simple as specifying SELECT DISTINCT instead of just SELECT in either your cursor or @sql.
Thank you for holding in there for me,  Great job
You're welcome; I'm glad it worked out for you.