create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

Create a variable type TABLE

I’ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. .The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [TESTE]

ON[dbo].[TABTESTE]

FOR INSERT

AS

DECLARE

@.YearTablenvarchar(4),

@.IdClientINT,

@.MyTableTABLE

(

IdClientINT,

SituNVARCHAR(50)

)

BEGIN

SET NOCOUNT ON;

SELECT @.YearTable = SITUACAO, @.IdClient = IdClient FROM INSERTED

SET @.MyTable = 'TABLE' & @.YearTable

UPDATE@.MyTable

SET

Situ= 'X'

WHEREIdClient = @.IdClient

END

GO

Erros:

Msg 156, Level 15, State 1, Procedure TESTE, Line 9

Incorrect syntax near the keyword 'TABLE'.

Msg 137, Level 15, State 1, Procedure TESTE, Line 17

Must declare the scalar variable "@.MyTable".

Msg 1087, Level 15, State 2, Procedure TESTE, Line 18

Must declare the table variable "@.MyTable".

I don't have much experience working with triggers but here's a shot...

-Try changing the @.MyTable to varchar(100)

-Declare another variable @.sql varchar(500)

set @.sql = 'update ' + @.MyTable + ' set situ = '''X''' where IdClient = ' + @.IdClient

exec (@.sql)

You may need to play with the number of single quotes around X to get the string to build correctly.

|||

can you explain what are you trying to do with this statement.

SET @.MyTable = 'TABLE' & @.YearTable

Are you trying to concatenate the string?

|||It seems to me that he thinks that TABLE variables are some kind of references/pointers or interfaces, and that he can use TABLE var with the existing table ('TABLE' & @.YearTable) of the identical structure as TABLE var.
I guess that he, in fact, wants to create and execute some dynamic SQL string based on the inserted values.|||TABLE var is a table just like any "normal" table, it resides in the memory or in tempdb. It si ont some kind of reference!
You can't set it to a string, just like you can't do that with any "normal" table.

Are you trying to update the appropritate table ('TABLE' & @.YearTable) depending on the inserted value (@.YearTable = SITUACAO ... FROM INSERTED)?

|||

hi MauricioBogo,

declare @.MyTable Table

(

IdClient int,

Suit nvarchar(50)

)

after you declare @.MyTable as a "Table"

in this Transaction, the @.MyTable is a "local database object" Already.

not a variable value.

this is why you can't do this → " SET @.MyTable = 'TABLE' + @.Yeartable "

and update @.MyTable .

you can try this, as below:

declare @.sSQL varchar(Max)

declare @.Mytable varchar(255)

select @.yeartable = SITUACAO , @.IDClient = IDClient From Inserted

set @.MyTable = 'TABLE' + @.Yeartable

set @.sSQL = 'update ' + @.MyTable + ' set Situ ='X'
exec (@.sSQL)

--or--

or add any script you need.

try it.

hoping this can help.

Best Regrads,

Hunt.

|||

Yes, I'm trying to concatenate the string, but I already changed to + . Right ?

Thanks

|||

Hunt

Ok it works, thanks

Mauricio

|||

Table variables need to be declare alone:

DECLARE

@.YearTable nvarchar(4),

@.IdClient INT

DECLARE

@.MyTable TABLE

(

IdClient INT,

Situ NVARCHAR(50)

)

sql

Create a user defined function in a user agnostic way

All,
I have two functions where one calls the other:
-- simplified example
create function plus(@.a as integer, @.b as integer)
returns integer
begin
return @.a + @.b
end
go
create function t(@.a as integer)
returns integer
begin
return cr_user.plus(10, @.a)
end
go
Is there any way to create those without explicitely mentioning the
database user ("cr_user" in this case)? Ideally I would like to put them
into a file and execute it with osql and those functions are created for
the current user. Until now I could not find a working placeholder to use
in "t" that will call the current user's "plus". (SUSER_SNAME does not
work.)
The only alternative seems to be ATM to always create functions for dbo
which is always defined.
Any ideas? TIA
Kind regards
robertWhy would you need functions that are owned by specific users? Does the
purpose of these functions depend on individual users?
Make it a practice to always qualify object names with the owner.
ML|||ML wrote:
> Why would you need functions that are owned by specific users?
You can only use functions with the owner as prefix and other parts of the
software insert the user as prefix when generating queries.

> Does
> the purpose of these functions depend on individual users?
No, other than that it is only used by a single user.

> Make it a practice to always qualify object names with the owner.
Why? To avoid name clashes? I prefer the unqualified version. It avoids
a lot of hassle and we don't have to take care of name clashes.
Regards
robert|||"Name clashes"?
There are no name clashes if common objects have the default owner (dbo). If
you created these obviously common functions with a qualified name (using db
o
as the owner), you could prevent any problems.
The pros of using qualified names are simply too numerous to mention here.
In SQL 2005 the owner is replaced by a more powerful quaifier - the schema -
maybe you should look into it to better understand its purpose.
I'm not criticizing your standards, but maybe they aren't as perferct as
you'd like to believe.
Please explain with an example why you need to create objects for every
user, when all of the objects are equal in purpose? And what do you mean by
"only used by a single user"? Is this a security issue?
ML|||ML wrote:
> "Name clashes"?
> There are no name clashes if common objects have the default owner
> (dbo).
But there would be if multiple objects with the same name but different
owners existed. I assumed you were referring to this situation when
promoting qualified names.

> If you created these obviously common functions with a
> qualified name (using dbo as the owner), you could prevent any
> problems.
> The pros of using qualified names are simply too numerous to mention
> here.
Impressive. Care to reveal some detail here? All I found so far was
faster access to execution plans and better reuse of execution plans of
stored procedures when called with qualified names.

> In SQL 2005 the owner is replaced by a more powerful quaifier - the
> schema - maybe you should look into it to better understand its
> purpose.
I might if I have the time. But ATM we're on 2k so I wouldn't want to
complicate things by mixing in concepts we can't use anyway. :-)

> I'm not criticizing your standards, but maybe they aren't as perferct
> as you'd like to believe.
I'm far from believing they are perfect. They just exist. And there is
some effort attached to changing them. Hence my conservatism. :-)

> Please explain with an example why you need to create objects for
> every user, when all of the objects are equal in purpose? And what do
> you mean by "only used by a single user"? Is this a security issue?
I don't create the same objects for every user. There is just one (1)
database user (apart from dbo). All tables, views, procedures and
functions are created unqualified and thus belong to this single user.
I'd like to stick with that simple policy. Unfortunately you can create
functions unqualified but you have to invoke them qualified. I don't see
why this is or has to be but apparently it's the way things are. Do you
have an idea why they did it this way?
I'll check whether the privileges our app requires are sufficient to
create the function for user dbo. That seems the second best alternative.
Hm, it seems creating is ok but invoking is prohibited. Darn. Seems like
I have to go down the preprocessing road.
Cheers
robert|||Ok, simply put most benefits of using qualified names are related to
performance. As you've mentioned. Another might be context of use, yet in
your case I see no need for that, since you in fact deal with a single user
(owner). In a multi role environment it could provide more clarity if method
s
that basically serve the same purpose for a variety of roles need to provide
special context-related features, which can be simplified using different
owners while keeping one name. E.g. a procedure to get product details might
return a different result for owner Engineering and a different one for owne
r
Marketing.
If you are using a single owner, why not use dbo?
But on the other hand, your only issue is with invoking functions, so either
way using a qualified name is mandatory. Simply because MS SQL Server says
so. :)
ML|||ML wrote:
> Ok, simply put most benefits of using qualified names are related to
> performance. As you've mentioned. Another might be context of use,
> yet in your case I see no need for that, since you in fact deal with
> a single user (owner). In a multi role environment it could provide
> more clarity if methods that basically serve the same purpose for a
> variety of roles need to provide special context-related features,
> which can be simplified using different owners while keeping one
> name. E.g. a procedure to get product details might return a
> different result for owner Engineering and a different one for owner
> Marketing.
Ok, thanks for clarifying.

> If you are using a single owner, why not use dbo?
I would but it seems the permissions we require for the DB user are
sufficient to create a function with owner dbo but not to invoke it later.
For the time being I'll stick with the preprocessing (i.e. the actual user
name is injected before SQL is executed).

> But on the other hand, your only issue is with invoking functions, so
> either way using a qualified name is mandatory. Simply because MS SQL
> Server says so. :)
Yep. Still I don't have an idea why this is so - I suspect the words
"historic" and "legacy" would be used in an explanation... :-)
Again, thanks for helping out!
Kind regards
robert

create a unique Random integer

Hi there,
I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key.

I have written the following function, but when i call it as a default value for a field, it does not produce a unique number.

CREATE FUNCTION GETNEXTID(@.CURDATE DATETIME)
RETURNS BIGINT
AS
BEGIN
RETURN (SELECT CAST(
CAST(DATEPART(YY,@.CURDATE) AS VARCHAR) +
RIGHT('0' + CAST(DATEPART(M,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(D,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(HH,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(SS,@.CURDATE) AS VARCHAR),2) +
RIGHT('00' + CAST(DATEPART(MS,@.CURDATE) AS VARCHAR),3) AS BIGINT))
END

Can anyone help?I would create a table as follows:

Create Table DatabaseId
(
DBId Int Identity,
DBName vachar (40) NOT NULL
)

When you import data from the different databases, prefix your unique identifier with DBId. That should give you an unique number across the DB's.|||Take a look at newid()

Create a UNC Share

hi all,

i'm trying to write a script just like "copy database wizard" operation, for detaching, copy and ataching a database from a remote sql server to a local server.

Now the problem is when i try to copy the mdf file from the sql remote server to my local server.

Is it possible to create a UNC share in the remote sql server and delete after with t-sql like the Copy Database wizard does?

hope i made my self clear enought.

regards

Things like this should be planned and not ad-hoc. Though it's possible...

Code Snippet

exec xp_cmdshell 'net share sharename=<drive path>'

Create a txt file and rename the file from a SP

I can populate a txt file using DTS.
How do i Rename the file using data from the table using
DTS or a stored procedure?You could rename the file using DTS :-
http://www.sqldts.com/default.aspx?292
You could also use the xp_cmdshell stored proc
--
HTH
Ryan Waight, MCDBA, MCSE
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:092701c3a51d$bc93bfe0$a401280a@.phx.gbl...
> I can populate a txt file using DTS.
> How do i Rename the file using data from the table using
> DTS or a stored procedure?

Create a trigger to update a row that's been inserted or updated

Hi

Apologies if this is a silly question

I have a basic table "Customer" which has

Id

Address1

Address2

Address3

Town

County

Postcode

SearchData

After I insert or Update a row in this table I need to update the SearchData column

with

UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE)

only for that Id

I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself.

Regards

David

Instead of using the trigger better you can go with Computed Columns...

here it is,

Code Snippet

Createtable Customer

(

IdintNOTNULL,

Address1varchar(100)NOTNULL,

Address2varchar(100)NOTNULL,

Address3varchar(100)NULL,

Townvarchar(100)NOTNULL,

Countyvarchar(100)NULL,

Postcodevarchar(100)NOTNULL,

SearchDataasUPPER(ADDRESS1)+UPPER(ADDRESS2)+UPPER(TOWN)+UPPER(POSTCODE)PERSISTED --Persisted only used on SQL Server 2005

)

|||I would not recommend using the 'PERSISTED' keyword unless you are attempting to solve a particular problem.|||

Hi

That was really helpful.

Thanks

David