Impossible Function

Jan 12, 2005

Hi! all

I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure

CREATE PROCEDURE [update_Alarm_1]

declare @datetime1 ?????

declare @datetime2 ?????

[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 0 ] = @datetime1



[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 1 ] = @datetime2



[SELECT date_part( 'minute', datetime1 - datetime2 )] = @result



AS UPDATE [Time_Result]

SET [Comment] = @Comment_2

WHERE
( [result] = @result)

Thanks in Advance

View 1 Replies


ADVERTISEMENT

Impossible Function

Jan 12, 2005

Hi! all

I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure

CREATE PROCEDURE [update_Alarm_1]

declare @datetime1 ?????

declare @datetime2 ?????

[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 0 ] = @datetime1



[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 1 ] = @datetime2



[SELECT date_part( 'minute', datetime1 - datetime2 )] = @result



AS UPDATE [Time_Result]

SET [Comment] = @Comment_2

WHERE
( [result] = @result)

Thanks in Advance

View 3 Replies View Related

Impossible SQL, Or Am I Being Stupid?

May 7, 2001

I have a table that has three fields:

CATEGORIES
----------
catID
description
parentCatID


Some categories have sub-categories. Therefore, some entries will have null
for parentID while others will point to another category.

I need to select all of the categories, but have them returned in the
correct parent/child order (parents BEFORE children). I can't seem to get this to work correctly. Is this not possible or am I just being stupid?? :)

Thanks for any assistance.

Mike V.

View 5 Replies View Related

Impossible Query?

Mar 12, 2008



First of all, here's a description of the three tables involved in the query:

clients

id
name

services

id
name

clients_services

id
client_id
service_id

As you can imagine, I have a list of clients and each client can have several services assigned to him.
So, I have trying to come up with a query that would allow me to do display the following information inside a DataGridView without the use of any additional code:

I would like to be able to display all the services included in the services table and then, depending on the client that is currently selected, to have each of the services display a checkbox in the DataGridView showing the services currently assigned to that particular client. Something like this:

The services table has the following entries: Research, Consulting, Development, Data Protection and Design
The selected client id is "2"
That client has the following services assigned to him: Development and Design
The DataGridView should then display:

[ ] Research
[ ] Consulting

Development


[ ] Data Protection

Design



Is this possible without writing any code?

View 5 Replies View Related

Impossible To Make A Select

Aug 24, 2005

I've created a database :  hotelselect * from hotel                             worksselect * from hotel where dpt='5'       doesn't work : generate a errorAnyone has a explanation ?create table hotel ([hoteID] int IDENTITY (5000,1) NOT NULL,[dpt] ntext NOT NULL) ON [primary]

View 3 Replies View Related

Replication - Sp_MSget_subscription_guid Impossible

Sep 1, 2004

We currently have a pull transactional subscription running from a remote 'PublicationServer' in another country to our local server.

After restarting our fire wall we get the following error message:
"The process could not execute '{call sp_MSget_subscription_guid(16)}' on 'PublicationServer'."

This message is followed by another error message: "Another distribution agent for the subscription(s) is running.", although nothing was changed to the configuration and no SQL processes were started or restarted.

When we restart the distribution job manually it runs as if nothing happened.

Does anyone know what exactly caused the failure, and how to prevent it ?

Philip

View 1 Replies View Related

Impossible To Megere 2 Sources:

Aug 21, 2007

Hello everybody,

I'm trying to create a merge between 2 tables, the first coming from Oracle and the second resident on SQL 2005. The result I'd like to retrieve would be a table composed from the original data contained in the SQL table, update with new or changed rows coming from Oracle (the 2 tables are identical, I'm building an update job..). The final table will be the same table used for data source.


I setup connections and everything seems to works, but when I link the Merge block with the 2 sources blocks, double clickin on the Merge Block, I notice that every SQL table's column is set to "ignore".
When I try to change it, SSiS says:

Failed to set property "OutputColumnLineageID" on input column ...

What's wrong? I've set IsSorted property to True and each column with the righ position...

I've also another question: actually my tables hasn't a "last update" column, so I think the merge "block" can't just update the SQL existing rows but it will add only newest rows. Is that right? How can I easily implement an "update check" in your opinion?

Thanks in advance and sorry for my bad english

View 1 Replies View Related

A Simple Group By Query Seems Impossible

Jul 20, 2006

I've been fighting with this all day there has to be an easy way to do this in a single query:

FOR EACH fac_id, get the INSPECTION_ID of the earliest start_date:

INSPECTION_IDFAC_IDSTART_DATE
3007200406280045662004-07-07
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003020600314582003-02-05
30072003012100214802003-03-25
30072003012100314802005-02-02
20082003123000114362003-12-30
30072004061600615662004-08-26
30072004061600115662001-08-26
30072004061600515662002-08-26
30072004061600215662003-08-26
30072004061600315662004-08-26
30072004061600415662006-08-26
30072004050400415692004-09-10

DESIRED OUTPUT
INSPECTION_IDFAC_IDSTART_DATE
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003012100214802003-03-25
20082003123000114362003-12-30
30072004061600115662001-08-26
30072004050400415692004-09-10

Am i missing something...
any help would be greatly appreciated...

View 1 Replies View Related

A Simple Group By Query Seems Impossible

Jul 20, 2006

I've been fighting with this all day there has to be an easy way to do this in a single query:

FOR EACH fac_id, get the INSPECTION_ID of the earliest start_date:

INSPECTION_IDFAC_IDSTART_DATE
3007200406280045662004-07-07
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003020600314582003-02-05
30072003012100214802003-03-25
30072003012100314802005-02-02
20082003123000114362003-12-30
30072004061600615662004-08-26
30072004061600115662001-08-26
30072004061600515662002-08-26
30072004061600215662003-08-26
30072004061600315662004-08-26
30072004061600415662006-08-26
30072004050400415692004-09-10

DESIRED OUTPUT
INSPECTION_IDFAC_IDSTART_DATE
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003012100214802003-03-25
20082003123000114362003-12-30
30072004061600115662001-08-26
30072004050400415692004-09-10

View 2 Replies View Related

Impossible To Use A Scalar UDF To Pass A Parameter To Another UDF?

Jul 23, 2005

Hello there!I am working with MS SQL Server 2000.I have a table function that takes an integer parameter and returns atable, and I can successfully use it like this (passing a literalas a parameter):SELECT * FROM MyTableFunction(1)or like this (passing a variable as a parameter):DECLARE @i AS intSELECT @i = 10...SELECT * FROM MyTableFunction(@i)Now, if I have another function -- a scalar function that returns aninteger, I cannot find a way to use it to specify the parameter valuefor the first function. For example, when I writeSELECT * FROM MyTableFunction( dbo.MyScalarFunction() )SQL Server issues the following complaint:Incorrect syntax near '.'.I am really perplexed: what I am doing wrong?Interestingly, if I re-write the second snippet asDECLARE @i AS intSELECT @i = dbo.MyScalarFunction()...SELECT * FROM MyTableFunction(@i)everything works just fine; however, this trick cannot be used as aworkaround because I need to pass result of one function as a parameterto another inside a view's code -- I cannot declare variables and writeany procedural code...Any ideas, especially workarounds, would be greatly appreciated.Thank you,Yarik.

View 2 Replies View Related

Creating XML Output...unusual/impossible Format?

Sep 23, 2005

Hi all,

I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.

The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)

The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.

Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?

I want my SYMBOL tag to be the actual SYMBOL for the stock fund.

Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.

Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!

Thanks!

View 6 Replies View Related

Point In Time Backup (impossible For Some Points?)

Sep 21, 2006

Hello,I am using SQL Server 2000 with SP4. I have a database with two fullbackups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30PM. Is there a possible way to do a point in time restore to 4:30 PM,that is between two full backups?When I try to use the transactional log backup that is taken at 5:30, Ican never specify a time before 5:00 PM. Is the transaction logtruncated at each full backup? If so, even if you take transactionallog backup every ten minutes, and full backups every once in a while,there will be some point in time which cannot be recovered to, namelythe time between a transactional log backup and a full backup. Take alog backup at 4:50, and full backup at 5:00 and you can never recoverto 4:55, can you?Any insight on the topic will be appreciated,Regards,M. Baris Caglar

View 3 Replies View Related

Desperate! Aspnet.mdf Screwed Up; User Login Impossible...

Aug 31, 2006

I hope anyone can help me with this. I am having problems connecting to the aspnet database. It
all started when I noticed that I could not log in to my webapplication
anymore under debug mode, although I was able to log in when remotely
accessing the website. I got the error message that the machineaspnet
user did not have access to the database:  "Login failed for user
'GM-FEDORSASPNET' "This was highly peculiar and rather annoying so I tried several things to straighten this out, but it only made things worse!I
read that this had to with permissions of the database in question, al
though I could not understand why permissions suddenly had changed. I
tried to change the rights by attaching it to Management Studio Express
and defining an aspnet login, but that failed because a login under
that name was not accepted. I tried to make a copy of aspnet.mdf, delete the original and rename the copy to aspnet.mdf. When that didn't work, I then deleted the aspnet.mdf, counting on it that it would be regenerated automatically, but alas. I located the aspnet_regsql.exe tool to regenerate the database, but got the following error message: System.Web.HttpException:
Unable to connect to SQL Server database. --->
System.Data.SqlClient.SqlException: An error has occurred while
establishing a connection to the server.  When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)What can I do? 

View 4 Replies View Related

Impossible? Sort On Dynamic Field (in Combination With Row_number)

Aug 17, 2007

I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by  m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1) 

View 9 Replies View Related

ASPNETDB Makes FULL TEXT SEARCH Impossible?

May 13, 2008

Hi There,
I have a database which I attached to Sql Server 2005 Express (With advanced features) and enabled full text search.  I created a FT catalog and FT index on a column and can successfully run the following query:SELECT expertise
FROM faculty_user
WHERE FREETEXT (expertise, 'Berry' )My issue is that full text search requires user instances to be turned off. Simply setting "User Instance=False" over in my web.config's connection string (in VWD) does not solve the problem.I still get the following error and I think it may be caused by my ASPNETDB. I can't find my connection string to ASPNETDB to turn off user instances and I can't diagnose my other connection strings because of it.Generating user instances in SQL Server is disabled

Here is a sample of my other connection strings:
<add name="myConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabase.mdf;Integrated Security=True;User Instance=False" providerName="System.Data.SqlClient"/>
From what I understand, I should also not be attaching the database from my connection string like this because it relys on User Instances? I am officially confused! Thanks for reading, -Derek

View 1 Replies View Related

Xp_cmdshell Problem SQL Server 2005; Or Perhaps I Am Attempting The Impossible...

Mar 7, 2008

I am using the xp_cmdshell to run a batch file that is trying to open the Adobe Acrobat Reader.

When I run the batch file in the command prompt, it opens perfectly.

When I run it in SQL with xp_cmdshell, it returns the contents fine, and I notice it tries to open Acrobat, but it is under the username "system". And I cannot get it to load in a visible area, and for some reason it doesnt seem to load properly under system.

Any ideas? Any Suggestions?

Please advise.

Marco

View 3 Replies View Related

FTP Task. / Validation Makes Mainframe Downloads Impossible.

Jul 24, 2007

When attempting to use the FTP task to download a file from a Mainframe system the task fails stating the filename as invalid because it doesn't begin with a "/".

Adding the slash to the front of the file name causes the mainframe to be unable to locate the file.

Commandline version of FTP.exe mimicks this behavior by working perfectly when the filename has no slash, and being unable to find the file when the slash is present.

Why does microsoft force a filename to start with a "/" and is there a way to make SSIS skip the validation phase for the FTP task?

View 7 Replies View Related

Absolutely Page-bottom Alignment On Report Footer: Impossible?

Mar 21, 2007

Relative newb to SSRS here, but the answer to this question evades me; answers and insight are appreciated.

Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements.

This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope.

Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.

So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.

Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.

I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.

Among what I've tried:

--Page footer (can't; databound)

--Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?).

--Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.

I may be having to explain to my client that you can't get there from here, and they may have to redesign their report. Does anyone have any insight?

Thank you for your time in reading this.

View 14 Replies View Related

[Reporting Services 2005] Access Impossible Of The Report Server

Mar 4, 2008

Hello,

SQL Server Reporting Services 2005 (SSRS) is installed on Vista Ultimate.
I cannot access to the http://localhost/ReportServer page. IE posts an internal error HTTP 500.0 (after IE required a Windows account).
All the configurations of Reporting Services are OK. In the Log file, there is nothing: the Web Services does not seem to be launched.

By continuing the investigations in IIS 7 (activation of the €śRules of follow-up of the requests having failed€?, I found the error following:

IIS Trace Detail Highlights
No. EventName Details Time
53. MODULE_SET_RESPONSE_ERROR_STATUS Warning
ModuleName="IsapiModule", Notification="EXECUTE_REQUEST_HANDLER", HttpStatus="500", HttpReason="Internal Server Error", HttpSubStatus="0", ErrorCode="Syntaxe du nom de fichier, de répertoire ou de volume incorrecte. (0x8007007b)", ConfigExceptionInfo="" 09:02:33.716

Translation of the french sentence: ErrorCode="Incorrect syntax of the file name, the directory or the volume."


I made a full research on Internet and in the documentation at my disposal and I did not find anything which can advance me towards a beginning of solution€¦



Thank you with all for the assistance which you will be able to bring to me.

View 4 Replies View Related

Impossible To Check Reporting Services Box On Install [Windows Server 2008]

Jan 29, 2008

Hye all, running WHS 2008, I try to install SQL Server 2005 with reporting services, but on install, in "components to install" wizard, the reporting services box is hided. I can't check this box :/ Do you know whats wrong ? if anyone knows the problem, thanks in advance.

View 1 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

A Function Smilar To DECODE Function In Oracle

Oct 19, 2004

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View 1 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Pass Output Of A Function To Another Function As Input

Jan 7, 2014

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.

View 5 Replies View Related

I Want A Function Like IfNull Function To Use In Expression Builder

Jul 24, 2007

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )



i hope to have the answer because i need it so much.



Maylo

View 7 Replies View Related

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )

Feb 4, 2008

Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View 4 Replies View Related

Function To Call Function By Name Given As Parameter

Jul 20, 2005

I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View 3 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007



Hi,



I am trying to create a inline function which is listed below.



USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.



Pleae help me to fix this error



thanks

Purnima

View 3 Replies View Related

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

Calling A Function From A Function?

Apr 11, 2008

Hi All

Yesterday Peso was gracious enough to help me with creating function/views/sp's

I took those examples and extended what had from excel into function in SQL

however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?

Here are excerpts two functions I have:

We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)

and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)

As you can see the first part of the both query are simlar. In particular the:

SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]

and also the variables @StartDate and @EndDate.

In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?

Thank you!

View 2 Replies View Related

Use Getdate Function In A Own Function

Jul 20, 2005

Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG

View 5 Replies View Related

SQL AVG Function

Apr 30, 2007

I have a table:tblRateUserUserIDJudge intUserIDJudged intscore int
this table contains the userid of the person giving a score (judge) and the person receiving the score (judged) and the score itself.
Each user can only score another user once.
tblRateUser content:judged   judge  score6 5 87 5 107 6 515 7 415 5 9
When a new score is inserted I want to get the average score for the rated user by counting all scores for that user and divide by the number of records.
 declare @avgscore decimal(4,1) set @avgscore=(select avg(score) from tblRateUser WHERE UserCodeJudged=@ID)    print 'avg score: '+ cast(@avgscore as nvarchar(15)) in the above example this results for usercode judged 7  in an average score of 7.0 whereas I would expect 7.5 (10+5)/2
what am I doing wrong?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved