sql database integrity: functions

Showing posts with label functions. Show all posts

Sunday, March 25, 2012

creating user defined parameters with input prompts

Hi,
i am trying to create a user defined function that return a table and it
prompts for two inputs
i have created the functions but how do i get the system to prompt me for
the inputs via a view ?
appreciate any advise
tks & rdgsSQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
need to have a client application do that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||maxzsim,
Something like this?
CREATE FUNCTION myfunc
@.param1 <datatype>,
@.param2 <datatype>
RETURNS TABLE
AS
RETURN
(<your select statement>
where col1 = @.param1
and col2 = @.param2)
GO
Mark.
"maxzsim" wrote:
> Hi,
> i am trying to create a user defined function that return a table and it
> prompts for two inputs
> i have created the functions but how do i get the system to prompt me for
> the inputs via a view ?
> appreciate any advise
> tks & rdgs|||Hi ,
does a view able to prompt the user for the parameters then , i can only
open a view as a query and where it allowed me to create parameters but it
seems like there's no way for me to save those changes at all
is there other way thru using a view ?
tks & rdgs
"Tibor Karaszi" wrote:
> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service. You
> need to have a client application do that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> > Hi,
> >
> > i am trying to create a user defined function that return a table and it
> > prompts for two inputs
> >
> > i have created the functions but how do i get the system to prompt me for
> > the inputs via a view ?
> >
> > appreciate any advise
> >
> > tks & rdgs
>
>|||Hi Mark,
in this case it does not run unless i input some values directly however,
i wanted to be prompted to enter the 2 parameters
will a view/stored procedure be able to do that ?
rdgs
"Mark Allison" wrote:
> maxzsim,
> Something like this?
> CREATE FUNCTION myfunc
> @.param1 <datatype>,
> @.param2 <datatype>
> RETURNS TABLE
> AS
> RETURN
> (<your select statement>
> where col1 = @.param1
> and col2 = @.param2)
> GO
> Mark.
> "maxzsim" wrote:
> > Hi,
> >
> > i am trying to create a user defined function that return a table and it
> > prompts for two inputs
> >
> > i have created the functions but how do i get the system to prompt me for
> > the inputs via a view ?
> >
> > appreciate any advise
> >
> > tks & rdgs|||gain, prompting a user need to be done in your client applications. You cannot write the whole
application in SQL only, SQL is only a data retrieval and modification language. The interaction
with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
something like that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
> Hi ,
> does a view able to prompt the user for the parameters then , i can only
> open a view as a query and where it allowed me to create parameters but it
> seems like there's no way for me to save those changes at all
> is there other way thru using a view ?
> tks & rdgs
> "Tibor Karaszi" wrote:
>> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service.
>> You
>> need to have a client application do that.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
>> > Hi,
>> >
>> > i am trying to create a user defined function that return a table and it
>> > prompts for two inputs
>> >
>> > i have created the functions but how do i get the system to prompt me for
>> > the inputs via a view ?
>> >
>> > appreciate any advise
>> >
>> > tks & rdgs
>>|||tks Tibor for taking time out to explain to me
Cheers
"Tibor Karaszi" wrote:
> gain, prompting a user need to be done in your client applications. You cannot write the whole
> application in SQL only, SQL is only a data retrieval and modification language. The interaction
> with the user need to be done with a client application, written in VB. C#, C++ Delphi Access or
> something like that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:4B01C6CF-392B-499F-8D9F-3824DF3AB2A2@.microsoft.com...
> > Hi ,
> >
> > does a view able to prompt the user for the parameters then , i can only
> > open a view as a query and where it allowed me to create parameters but it
> > seems like there's no way for me to save those changes at all
> >
> > is there other way thru using a view ?
> >
> > tks & rdgs
> >
> > "Tibor Karaszi" wrote:
> >
> >> SQL Server cannot prompt a user for anything, as it runs on the server machines, as a service.
> >> You
> >> need to have a client application do that.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> >> news:8AB52501-7876-4870-ADA1-6DC8BB93785E@.microsoft.com...
> >> > Hi,
> >> >
> >> > i am trying to create a user defined function that return a table and it
> >> > prompts for two inputs
> >> >
> >> > i have created the functions but how do i get the system to prompt me for
> >> > the inputs via a view ?
> >> >
> >> > appreciate any advise
> >> >
> >> > tks & rdgs
> >>
> >>
> >>
>
>

creating user defined parameters with input prompts

Creating UDF?

Creating UDF In SQL Server 2005?

I find PLENTY of examples of User Defined functions. However, I
struggle to see how/where to SAVE this function! I can go under the
database, Programmability, Functions, Scalar Functions, and create a
scalar function. However, when I click SAVE I get prompted to save the
.sql file to disk. If I close that (query) tab, then the function is
gone as well.
How do I persist the function in a database? I should note that this is
for SQL Server 2005.<mikes@.vmsmailingservices.com> wrote in message
news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
> I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under
the
> database, Programmability, Functions, Scalar Functions, and create
a
> scalar function. However, when I click SAVE I get prompted to save
the
> .sql file to disk. If I close that (query) tab, then the function
is
> gone as well.
> How do I persist the function in a database? I should note that
this is
> for SQL Server 2005.
>
mikes,
The .sql files are just text files with another extension. Of
course you want to save them as your scripts, but if you want to
check out your stored procedures and functions in the database,
then:
SELECT R1.*
FROM INFORMATION_SCHEMA.ROUTINES AS R1
Sincerely,
Chris O.|||Hi Mike,
When you run the text that you have just created then teh SQL server creates
the object in the database. The text is no longer needed by the SQL Server.
You should however save this text to a file and that file can be stored
anywhere you like
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
<mikes@.vmsmailingservices.com> wrote in message
news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
>I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database? I should note that this is
> for SQL Server 2005.
>|||Why save it when sql will give you the create statement any time you need
it?
William Stacey [MVP]
"GregO" <grego@.community.nospam> wrote in message
news:e8rKpwx7FHA.2816@.tk2msftngp13.phx.gbl...
> Hi Mike,
> When you run the text that you have just created then teh SQL server
> creates the object in the database. The text is no longer needed by the
> SQL Server. You should however save this text to a file and that file can
> be stored anywhere you like
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
> <mikes@.vmsmailingservices.com> wrote in message
> news:1132627572.039473.90980@.g47g2000cwa.googlegroups.com...
>|||Hello mikes@.vmsmailingservices.com,

> I find PLENTY of examples of User Defined functions. However, I
> struggle to see how/where to SAVE this function! I can go under the
> database, Programmability, Functions, Scalar Functions, and create a
> scalar function. However, when I click SAVE I get prompted to save the
> .sql file to disk. If I close that (query) tab, then the function is
> gone as well.
> How do I persist the function in a database? I should note that this
> is for SQL Server 2005.
Rephrasing some of the answers you've gotten thus far, as soon as you execut
e
the CREATE FUNCTION statement, a "complied" version of the function is commi
tted
to the database. There's no need to save the text version of it, since, when
you issued that same CREATE FUNCTION statement, SQL Server also saved that
to the database.
Make sense?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi William
Seriously?
What happens when some deletes it or changes the structure. You need
something to compare it to. There so many reasons for keeping the command
syntax and next to none for not keeping it. When in doubt keep the command.
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OVdiJWy7FHA.472@.TK2MSFTNGP15.phx.gbl...
> Why save it when sql will give you the create statement any time you need
> it?
> --
> William Stacey [MVP]
> "GregO" <grego@.community.nospam> wrote in message
> news:e8rKpwx7FHA.2816@.tk2msftngp13.phx.gbl...
>|||>
> Rephrasing some of the answers you've gotten thus far, as soon as you exec
ute
> the CREATE FUNCTION statement, a "complied" version of the function is com
mitted
> to the database. There's no need to save the text version of it, since, wh
en
> you issued that same CREATE FUNCTION statement, SQL Server also saved that
> to the database.
> Make sense?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
This helps a lot, thanks everyone!
a couple more related questions, based on your responses...
How does the DB engine manage a new function of the same name? Prompted
to override?
How can one determine a list of the functions in a database?
How is (function) debugging done, if the function is internal to the DB
Engine?|||Hello mikes@.vmsmailingservices.com,

> How does the DB engine manage a new function of the same name?
> Prompted to override?
No, unlike other development tools, most of the SQL Server tools *trusts*
the operator to know what he or she is doing. Most shops have a CVS (or simi
lar)
set up for storing scripts for objects. MS is offering one with team system
too. Native integration of that into SQL Server is probably a couple of vers
ions
away I suspect.

> How can one determine a list of the functions in a database?
select * from sys.objects where type_desc like '%_FUNCTION'

> How is (function) debugging done, if the function is internal to the
> DB Engine?
Like any other environment, the execution engine know that it has breakpoint
s
that it yields control to another process. It just so happens that SQL Serve
r's
engine uses a common Visual Studio API for debugging. No magic.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Sunday, February 19, 2012

Creating functions

Well, I just downloaded SQL 2005 Express to have a play. Good old Bill
says "up and running in 20 minutes"

Well 5 hrs, 5 reboots and 4 full un-install and re-install later I have
finally got it working. About what I expected really.

Anyway, my question relates to creating user defined functions. I've 10
yrs exp in MS Access and VB, and user ent Manager to create views on
the work server, but havnt done much with functions.

I'm using the Management Studio Express.

I've opened a new view and copied the following sample code but it wont
let me save it.

--------
IF OBJECT_ID(N'dbo.GetWeekDay', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetWeekDay;
GO
CREATE FUNCTION dbo.GetWeekDay -- function name
(@.Date datetime) -- input parameter name and data
type
RETURNS int -- return parameter data type
AS
BEGIN -- begin body definition
RETURN DATEPART (weekday, @.Date) -- action performed
END;
GO
-------------

When I try to save it it says
"incorrect syntax near the key work 'IF'
incorrect syntax near 'GO'
CREATE FUNCTION must be the first statement in a query batch
must declair the scalar variable "@.Date"

Whats wrong with the syntax?

Thanks in advance

GrantHi frnd
there is no need for ' ; ' inside sql its on c++...
remove ' ; ' and try it might work...|||ok I'm confused about functiosn. In the past I have used MS Access ADP
to create function in SQL Server and they have shown up as a separate
object under views. But now I have executed a Create Function and it
says its execute ok, and I can run a select statement and call the
function and return valurs, so its obvoiusly there (somewhere), but its
nto showing up under views, so where to i view and edit functions??

Thanks

Grant|||Functions are under Functions (not Views).

g...@.technologyworks.co.nz wrote:

Quote:

Originally Posted by

ok I'm confused about functiosn. In the past I have used MS Access ADP
to create function in SQL Server and they have shown up as a separate
object under views. But now I have executed a Create Function and it
says its execute ok, and I can run a select statement and call the
function and return valurs, so its obvoiusly there (somewhere), but its
nto showing up under views, so where to i view and edit functions??
>
Thanks
>
Grant

|||(grant@.technologyworks.co.nz) writes:

Quote:

Originally Posted by

There is a Functions node under Programmability.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sunday, March 25, 2012

creating user defined parameters with input prompts

creating user defined parameters with input prompts

creating user defined parameters with input prompts

Creating UDF?

Creating UDF In SQL Server 2005?

Sunday, February 19, 2012

Creating functions

sql database integrity

Blog Archive

About Me