Pass Back One Value From UDF Having Found It In A Table
Feb 8, 2007
How do I pass back only one value, such as TotalJobPrice from the tblJobRecords when I know which JobID to Select. All I want is the TotalJobPrice returned from the UDF, not a record from the table. I can write a UDF that will return a table, but I don't know how to get the one field of data from that table of one row that can be returned from a UDF that returns a table. I want to be able to write something like this: @TotalJobPrice = fnTotalJobPrice(@JobID)
Hope that is clear.
Thanks in advance,
Is there no way to pass identity info back through SqlDataSource? You can only do that with ADO.NET code? In other words, if I want to run a complex INSERT statement to a table that uses Identity, I can't take that key value back through something like SCOPE_IDENTITY() and use it? I know how to do this with ADO code, but I can't figure out how to do it purely with SqlDataSource. I was hoping to do this without having to write a new Insert statement -- just using the one that's already in the SqlDataSource control. But there doesn't seem to be any facility for Identity in there. I tried embedding a separate select statement after the insert statement and a semi-colon, but that didn't seem to do anything. Thanks!
I'm using an Access2K front end on a SQL Server2K backend.I use Scope_Identity() in a lot of stored procedures to pass thenewwly inserted record's unique ID to the next select statement in thesame stored procedure.What I'm wondering is how I can pass the Scope_Identity back to thecalling application.I'm hoping someone can show me the SP code and the aceess code neededto accomplish the following:I insert a new record in a table with a stored procedure. The SPpasses the uniqueID (Scope_Identity) back to MS Access, then MS Accessuses the uniqueID when calling another function...thanks
I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.
Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?
I have a function in VBA (Access) that passes a sql string into a passthrough query that is server side executed
I changed the inner joins and left joins to where statements but this particular sql string crashes with the titled error folowed by the multi-part identifier "task_backup.activityid" could not be bound (#4104) and then it repeats that one more time in the same message
Which i think the second "sub message" is for the null criteria
INSERT INTO NewTasksBeingAdded ( [Activity ID], [WBS Code], [Activity Name], Area, Line) SELECT [TASK Excel Data].[Activity ID],[TASK Excel Data].[WBS Code], [TASK Excel Data].[Activity Name], [TASK Excel Data].Area, [TASK Excel Data].Line FROM [TASK Excel Data] where [TASK Excel Data].[Activity ID] = TASK_BackUP.[Activity ID] and TASK_BackUP.[Activity ID] Is Null
I have a query which is quite complex. It's based on a set of data ina complex view which takes the data from several tables.In this complex query, if I allow the various parts of the query towork on the results of the view (MISView), it can take 15 minutes torun (eek !), however, if I create a temporary table with the data fromthe view and then use that for the remainder of the query, it runs inapprox 20 seconds.Now, I have examined the execution plan (my new favourite toy) andthere is a difference (as expected). However when looking at the partof the query that takes up most of the time, it shows that it bringsback 109,645,866 records from a table (Credit) that contains 13,002records. This table is one that is referenced in the view (MISView)which contains 13,653 records and does get some of it's data from thetable which is scanned (Credit).For the record, we don't have any tables with over 100,000 records in,so 109 million rows is going some for us. The part of the query thatruns slow does reference another copy of itself but this is necessaryfor the equation that is being run.Now I'm OK with why it's doing the table scan, but why does it bringback substantially more data than is in the table ? Is it somemultiple of the number of records that it's trying to work out. Iassume it tries to run a seperate plan for the view as part of it'sprocess.Ideally, I'm still going to go down the route of the temporary table,but I would like to understand more about what it does first as Idon't like leaving things unanswered.Any help would be appreciated.
I have a DTS package that has two steps. The first steps executes a sql statement that selects data into a temporary table. The next step emails the results of a "select * from #mytable" to me. I ran the job today and did not get an email from it. So, I then copied both statements to Query Analyzer and ran them in the same window. The SELECT query ran fine but the xpsendmail part gave me an error that #mytable does not exist.
This is my query
drop table #update_status select col1,col2,col3 into #update_status from mydb.mytable
go
EXEC master..xp_sendmail @recipients = 'fkearney', @query = 'select * from #update_status', @subject = 'status updated', @message = 'These records had a status update:', @attach_results = 'TRUE', @width = 250
Hi all, this is REALLY weird, I can t seem to make heads or tails of it. but from my understanding the each datatype has set contrstraint assigned to it ( example int datatype can only except non-decimal numeric values ) as well as a datetime datatype can only except a vaild date.
Though oddly i have found the following dates in a table ( in addtion the when aby kind of data operation is performed on the table sql returns the following error: Server: Msg 8630, Level 16, State 1, Line 1 Internal Query Processor Error: The query processor encountered an unexpected error during execution.
Here are some of the dates that were present in the table: DateColumn1 ----------- 1900-01-01 857:44:45.813 1900-01-01 872:51:16.427 1900-01-01 872:54:57.440 1900-01-01 873:09:32.107 1900-01-01 873:13:10.560 1900-01-01 873:16:49.867 1900-01-01 888:27:00.640
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
I have some troubles getting done a lookup... data i get from a legacy system has to be cleaned up and split up into normalized form. i get invoice data and want to split up invoices into a separate table as customers. therefore i lookup if the customer of the current line is not already in my newly created customer-table. if it isn't there (error output of lookup transformation) i insert it into the customer-table.
The problem is that after the customer is inserted into the customer-table, it still is not found by the lookup transform because the lookup uses caching to hold the reference table (and so is the exact same customer inserted again and again and again). Is there any way to disable caching and let the lookup transformation do a select for every new row it gets? or at least refilling the cache if some event happens?
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
Can a table be passed as parameter to a T-SQL procedure or function?One can define a temporary table within a procedure, and the system then manages its lifetime, keeps it separate from other instances executing the same procedure, etc: - CREATE PROCEDURE dbo.name (@Parameter1, @Parameter2) AS BEGIN DECLARE @name TABLE (column list) etcPerfect: but I need to be able to have another [recursive] procedure process this table. How do I make it available to the second procedure? If I write EXEC procedure @Namean error message "Must declare the variable '@Name'" is produced. Within the 2nd procedure, an attempt to name a table within the parameter list results in another error message, "Incorrect syntax near the keyword 'TABLE'". If I can't pass it as argument/parameter, then how do you process a temporary table in another procedure? Do I pass a cursor to it? Or is it quite impossible?Regards, Robert Barnes.
Hi Friends, Is it possible to pass the name of table to the User Defined Function in SQL and use it inside the function. Any help would be appreciated. Fazal.
I am writing a (very simple) stored procedure that counts records on a table. The stored proc is as follows: ------------------------------------------ CREATE PROCEDURE GetRecordCount @TableName varchar(30), @NumRecs int OUTPUT AS
DECLARE @sqlcmd varchar(250) SET @sqlcmd = 'SELECT @NumRecs = COUNT(email) FROM ' + @TableName + 'WHERE Include = "Y"' EXEC (@sqlcmd) ------------------------------------------ The intention is to return variable @NumRecs. I am calling the routine from Query Analyzer as follows:
When I execute the above line, I get an error message: Server: Msg 137, Level 15, State 1, Line 0 Must declare the variable '@NumRecs'.
If I replace the "SET @sqlcmd = .." AND "EXEC(.." with: SELECT SELECT @NumRecs = COUNT(email) FROM MyTable etc. i.e. without passing the table name, the process works fine. How can I construct an sql command using a parameter?
I have several tables which are all identical in columns and datatypes. I want to create a function which does some calculations on this tables.
Since I just want to create one function I Need to specify the table on which the function should work on. That means i want to pass the table Name as a Parameter to the function.
I have a procedure where I am inserting some elements into #Table and then finally get the datset I need. Now when I am using this procedure as dataset to my report, it throws up the following error:
Invalid object Name "#TEMP2".
The data that I retrieve is similar to the data that I get from this query in the post by Manivannan.D.Sekaran http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1
Is it because my columns are generated on the fly that I am not able to retrieve the column headers appropriately. If so can someone suggest a way over to this?
I am not sure should I posting it here or in T-SQL Forum.
I renamed a database table, and then try to create a view with that table. However, the renamed table does not appear in view designer. I have to close and re-open SQL Server Management studio 2014.
Hi Friends,Is it possible to pass a table as a parameter to a funtion.whos function declaration would look some thing like this....ALTER FUNCTION TempFunction (@TempTable TABLE, @nPId INT)my problem is: i have to access a temporary table created in an SP ina functionALTER PROCEDURE MySPBEGIN....DECLARE @TmpTable TABLE(...)....TempFunction(@TmpTable)....ENDThanksArunDhaJ
execute sp_executesql @strQuery statement in my SP. and My is @strQuery = "Insert into @tmp_tbl select ...". I have created @tmp_tbl using "
DECLARE @tmp_tbl TABLE(rownum ...... ".
its giving me error "
Must declare the table variable "@tmp_tbl". while executing : execute sp_executesql @strQuery statement.
How to pass that table variable with executesql ?? As i understand, i created table @tmp_tbl in my SP and trying to access it inside system SP sp_executesql . so its not working.
Is there a way to pass table variable as parameter to sp_executesql ??
I am trying to execute an SQL task in a ForEach container that is the table name for a select statment I want to execute (table name is passed from a rowset created before the ForEach container).
ForEach Container is looping correctly (I've used a watch to make sure each of the table names in the previous rowset are being passed correctly). I want to use the table name to execute the following query:
Select count(*) as TableRowCount from MyLinkedServer...?
(with ? as the table name)
Problem I am having is that the SQL Task Editor keeps placing a space between the last period and the parameter and then telling me it cannot parse the SQL.
I tried:
Declare @SQL varchar(255)
Set @SQL = 'Select count(*) as TableRowCount from MyLinkedServer...' + ?
Exec(@SQL)
Same error as above. My parameter is varTableName
If I used the Declare and hardcode the table name (i.e. MyLinkedServer...MyTable), and it works fine. Just doesn't seem to want to allow the table name as a parameter...
Thoughts, ideas??? Am very new to SQL 2005 but did the above with DTS... Thank you for any assistance...
I have converted a access database to sql server 2000 for some reason i keep getting this error base table not found. I know the sql database is there and i also know the table is there. I'm not sure why i keep getting this error..
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type FROM Table1 t1 WHERE t1.Key2 IS NULL AND t1.Type LIKE 'TypeA' AND t1.Key1 NOT IN (SELECT Key1 FROM Table1 t2 WHERE t1.Key1 = t2.Key2 AND t1.Key1 <> t2.Key1 AND t2.Type LIKE 'TypeB')
