SQL Fetch And Null - SQL Server Forums

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)

SQL Fetch And Null

Author

Topic

Schnoz
Starting Member

4 Posts

Posted - 2009-08-13 : 14:39:13

I have a stored procedure that is inserting a bunch of rows from a table into another table in another database. First of all, I am using a fetch statement to run through each row. The only problem is, the fetch is only pulling rows that don't have a null in them (anywhere in them). Some rows have a null in the street column and some in the zip code column. The fetch won't pull any of those rows. It will only pull the rows that have complete data. Is there any way to get the fetch to pull the rows that have the null value as well as all other rows? I want to input that column that has null as a null in the new table on the other database.

Schnoz
Starting Member

4 Posts

Posted - 2009-08-13 : 14:40:34

BTW, here is the stored procedure if anybody needs to look at the code:

Declare @ID nvarchar(255)
Declare @LastName nvarchar(255)
Declare @FirstName nvarchar(255)
Declare @Street nvarchar(255)
Declare @City nvarchar(255)
Declare @Zip nvarchar(255)
Declare @State nvarchar(255)
Declare @Phone nvarchar(255)
Declare @EmailAddress nvarchar(255)

BEGIN TRANSACTION

DECLARE c1 CURSOR FOR
SELECT ID, LastName, FirstName, Street, City, Zip, State, Phone, EmailAddress
FROM Caspa_eSubmitted

OPEN c1
FETCH NEXT FROM c1
INTO @ID, @LastName, @FirstName, @Street, @City, @Zip, @State, @Phone, @EmailAddress

WHILE @@FETCH_STATUS = 0
BEGIN

Declare @State_Num varchar(100)
Set @State_Num = (SELECT State_Num FROM SRPDB.dbo.DomState WHERE StateAbbrev = @State)

DECLARE @SQL varchar(8000)
SET @SQL='
INSERT INTO SRPDB.dbo.tbImportStudent
(LastName,FirstName,
Street1, City, ZipCode,SATE_PKId,
Phone1Number, EmailAddress,
Priority, InsWho, InsDate, OVER_PKID) VALUES
(''' + @LastName + ''',''' + @FirstName + ''',''' + @Street + ''',''' + @City + ''',''' + @Zip + ''',' + @State_Num + ',
''' + @Phone + ''',''' + @EmailAddress + ''',
3,''Online Application'',getdate(),1)'
PRINT @SQL
EXEC(@SQL)

FETCH NEXT FROM c1
INTO @ID, @LastName, @FirstName, @Street, @City, @Zip, @State, @Phone, @EmailAddress

END
CLOSE c1
DEALLOCATE c1

COMMIT TRANSACTION

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-13 : 15:15:31

Maybe I'm missing something...but won't this do??

INSERT INTO SRPDB.dbo.tbImportStudent 
           (LastName, 
            FirstName, 
            Street1, 
            City, 
            ZipCode, 
            SATE_PKId, 
            Phone1Number, 
            EmailAddress, 
            Priority, 
            InsWho, 
            InsDate, 
            OVER_PKID) 
SELECT a.ID, 
       a.LastName, 
       a.FirstName, 
       a.Street, 
       a.City, 
       a.Zip, 
       b.State_Num, 
       a.Phone, 
       a.EmailAddress, 
       3, 
       'Online Application', 
       getdate(), 
       1 
FROM   Caspa_eSubmitted a 
       INNER JOIN SRPDB.dbo.DomState b 
         ON b.StateAbbrev = a.State

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources