Help with passing a function to a table

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

General SQL Server Forums

New to SQL Server Programming

Help with passing a function to a table

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-12 : 15:47:30

Or with passing a kidney stone, which might be less painful.....
First, what i know and what works:
What i have working now is this....There is a function in our db that generates a random, unique id number. That function looks like this when i script it out as a "select to": SELECT [SCADMIN].[dbo].[fnsc_GetUniqueID] (
<@Guid, uniqueidentifier,>)
In my tables, where i need to execute that function i use: INSERT TO (UNIQUEKEY) VALUES(SCADMIN.dbo.fnsc_GetUniqueID(newid()))

and the function fires off correctly, giving me the value i need.

Now, where it's not working. I have another column which compares two dates and if the difference between the two dates is lower than a certain number, it applies a "0" as the value to that cell, if it is higher than a certain number, then it applies a "1" to that cell. It arrives at this decision, allegedly by the use of another function. That function, when scripted out as a "select to" statement, looks like this:

SELECT [CFG].[dbo].[fnsc_AgeOnDate] (
<@inDOB, datetime,>
,<@inCompareDate, datetime,>)
GO

What is my syntax in using that function like the previous one to get it to fire off correctly for my inserts?????

This is my first time working with functions on this level.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 01:34:37

you can use like

INSERT INTO Table (yourdestinationcolumn)
SELECT [CFG].[dbo].[fnsc_AgeOnDate] (firstdatecol,seconddatecol)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-13 : 02:43:38

Hi, In order to insert value in a table from a function you can use some thing like below
Eg Create table temp(mydate Datetime)
insert into temp(mydate)
select getdate()

Here getdate is a function

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-13 : 04:36:32

I'm a little confused about what's not working, but this is the general syntax when inserting the result of functions to another table (a small elaboration on visakhs suggestion only):



DECLARE @CompareDate datetime 
SET @CompareDate = '2000-01-01'

INSERT INTO MyDestTable (UniqueID, EmployeeID, DOB, CompareDate, AgeOnDate)
SELECT 
  SCADMIN.dbo.fnsc_GetUniqueID(newid()),
  EmployeeID,
  DOB,
  @CompareDate,
  CFG.dbo.fnsc_AgeOnDate (DOBColumn, @CompareDate)
FROM myEmployeesTable
WHERE DOBColumn >= '1950-01-01'

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-13 : 09:58:42

When i use viasakhs suggestion i get "invalid column name" errors for the firstdatecol and seconddatecol when i transpose my column names in there (DOB, REPORTDATE). Those columns exist in my table and they are the columns from which the function should be making it's comparison. Keep in mind also, i need to execute this insert over 6,000 times on different rows. I've tried to declare the two values that are in the function itself (@inDOB, and @CompareDate). That executed the function within the insert, but it did not produce the result it was supposed to (a 1 or zero being placed in the OTTIC column. I've also tried to declare those values in the function and "set" them to the names of the columns (Declare @inDOB, Declare @CompareDate, set @inDOB=DOB, and set @inCompareDate=ReportDate), but i get syntax errors right off, indicating that it does not recognize the columns "DOB" and "ReportDate" in my table, even though they are there. I'm also using the correct "Use (databasename)" at the start of the script.

Ugh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 10:08:27

quote:
Originally posted by WJHamel

When i use viasakhs suggestion i get "invalid column name" errors for the firstdatecol and seconddatecol when i transpose my column names in there (DOB, REPORTDATE). Those columns exist in my table and they are the columns from which the function should be making it's comparison. Keep in mind also, i need to execute this insert over 6,000 times on different rows. I've tried to declare the two values that are in the function itself (@inDOB, and @CompareDate). That executed the function within the insert, but it did not produce the result it was supposed to (a 1 or zero being placed in the OTTIC column. I've also tried to declare those values in the function and "set" them to the names of the columns (Declare @inDOB, Declare @CompareDate, set @inDOB=DOB, and set @inCompareDate=ReportDate), but i get syntax errors right off, indicating that it does not recognize the columns "DOB" and "ReportDate" in my table, even though they are there. I'm also using the correct "Use (databasename)" at the start of the script.

Ugh.

if values are passed from table column you need to specify like


INSERT INTO Table (yourdestinationcolumn)
SELECT [CFG].[dbo].[fnsc_AgeOnDate] (DOB, REPORTDATE)
FROM YourTable

make sure you replace indicate names with actual names (ie for tables)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-13 : 10:14:21

When i add that, it throws the entire rest of that insert line into syntax mayhem. See here:
INSERT INTO ARREST.dbo.Arrest (ARRESTTYPE,WARSTATUS,OTTIC,ARRESTNO,REPORTDATE,COURTCASE,LNAME,FNAME,MNAME,TITLE,DOB,RACE,SEX,HEIGHT,WEIGHT,EYES,SSN,OCCUR_DATE,UNIQUEKEY,ECSOID,CourtName) VALUES ('W','W',SELECT cfg.dbo.fnsc_AgeOnDate(DOB,ReportDate)FROM arrest..Arrest,'GCSOARR0022833','1/2/03','02000655CFMA','Doe','John','','','1/20/79','W','M','5 05',130,'BRN','999 99 9999','9/28/02',SCADMIN.dbo.fnsc_GetUniqueID(newid()),'GCSOMNI0022833','TPL');

Everything after that third insert shows syntax errors.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 10:58:56

why dont you post full statement in beginning itself so that you can avoid the confusion. it should be like


INSERT INTO ARREST.dbo.Arrest (ARRESTTYPE,WARSTATUS,OTTIC,ARRESTNO,REPORTDATE,COURTCASE,LNAME,FNAME,MNAME,TITLE,DOB,RACE,SEX,HEIGHT,WEIGHT,EYES,SSN,OCCUR_DATE,UNIQUEKEY,ECSOID,CourtName) 
SELECT 'W','W',cfg.dbo.fnsc_AgeOnDate(DOB,ReportDate),'GCSOARR0022833','1/2/03','02000655CFMA','Doe','John','','','1/20/79','W','M','5 05',130,'BRN','999 99 9999','9/28/02',SCADMIN.dbo.fnsc_GetUniqueID(newid()),'GCSOMNI0022833','TPL'
FROM arrest..Arrest

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-13 : 11:07:43

I'm sorry to be such a pain, but i'm just trying to get this figured out. The full statement, which is identical to above, gives me "unknown column name" for DOB and REPORTDATE (again, these are verified columns within this table) within the function insert and "incorrect syntax" when i place the "FROM" statement at the end:

Use ARREST

INSERT INTO ARREST.dbo.Arrest (ARRESTTYPE,WARSTATUS,OTTIC,ARRESTNO,REPORTDATE,COURTCASE,LNAME,FNAME,MNAME,TITLE,DOB,RACE,SEX,HEIGHT,WEIGHT,EYES,SSN,OCCUR_DATE,UNIQUEKEY,ECSOID,CourtName) VALUES ('W','W', cfg.dbo.fnsc_AgeOnDate(DOB,ReportDate),'GCSOARR00228XX','1/2/2003','02000655CFMA','Doe','John','','','1/20/1999','W','M','5 05',130,'BRN','999 99 9999','9/28/02',SCADMIN.dbo.fnsc_GetUniqueID(newid()),'GCSOMNI00228XX','TPL', FROM arrest..arrest

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 11:09:19

please use it as suggested.
your used query is different from mine. I dont have VALUES in my query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-13 : 11:22:23

Thank you, but, when i use your query as it's constructed, the syntax errors are taken care of, but it does not actually insert the data into the table. The query fires off normally, but it shows "(0 rows(s) affected) and no data is inserted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:16:35

i'm not sure you're doing it right. can you post your full query please?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-13 : 12:18:43

Use Arrest

INSERT INTO ARREST.dbo.Arrest (ARRESTTYPE,WARSTATUS,OTTIC,ARRESTNO,REPORTDATE,COURTCASE,LNAME,FNAME,MNAME,TITLE,DOB,RACE,SEX,HEIGHT,WEIGHT,EYES,SSN,OCCUR_DATE,UNIQUEKEY,ECSOID,CourtName)
SELECT 'W','W',cfg.dbo.fnsc_AgeOnDate(DOB,ReportDate),'GCSOARR0022833','1/2/03','02000655CFMA','Doe','John','','','1/20/79','W','M','5 05',130,'BRN','999 99 9999','9/28/02',SCADMIN.dbo.fnsc_GetUniqueID(newid()),'GCSOMNI0022833','TPL'
FROM arrest..Arrest

I would then have to repeat this insert with different data, to the same fields, another 6,000 times for other individuals.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:20:35

do you have any records in arrest table? what does below return?


SELECT COUNT(*)
FROM arrest..Arrest

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-13 : 12:24:42

I am currently starting from a clean table, but after this is finished, i will be inserting the data into a table which already contains data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:31:45

then how do you think your previous will return any records. Your Arrest table is blank right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-13 : 12:33:12

correct. It is currently blank. I am trying to use these insert statements to get this data into the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:35:23

Sorry your explanation doesnt make any sense. if thats the case what the use of calling udf like this?

SELECT 'W','W',cfg.dbo.fnsc_AgeOnDate(DOB,ReportDate),'GCSOARR0022833','1/2/03','02000655CFMA','Doe','John','','','1/20/79','W','M','5 05',130,'BRN','999 99 9999','9/28/02',SCADMIN.dbo.fnsc_GetUniqueID(newid()),'GCSOMNI0022833','TPL'
FROM arrest..Arrest

it wont return any values as table is blank.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:38:25

my other guess is you may be trying to do this


INSERT INTO ARREST.dbo.Arrest (ARRESTTYPE,WARSTATUS,OTTIC,ARRESTNO,REPORTDATE,COURTCASE,LNAME,FNAME,MNAME,TITLE,DOB,RACE,SEX,HEIGHT,WEIGHT,EYES,SSN,OCCUR_DATE,UNIQUEKEY,ECSOID,CourtName) 
SELECT 'W','W',cfg.dbo.fnsc_AgeOnDate('1/20/79','1/2/03'),'GCSOARR0022833','1/2/03','02000655CFMA','Doe','John','','','1/20/79','W','M','5 05',130,'BRN','999 99 9999','9/28/02',SCADMIN.dbo.fnsc_GetUniqueID(newid()),'GCSOMNI0022833','TPL'
FROM arrest..Arrest

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources