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.
| Author |
Topic |
|
sarahmfr
Posting Yak Master
214 Posts |
 Posted - 2010-04-09 : 17:40:51
|
| In oracle we can let the datatype of the stored procedure parameter map to the datatype of the columns example CREATE PROCEDURE [CheckCustomer]@customerid onl_Customers.UserID%type Can we do that in sqlstored procedure parameter data type automatically gets the column datatypeQuestion: Is there a way so that in a stored procedure instead of assigning the data type of the parameter I can pull it from the columndata type.Because sometimes we change the column datatypes and we do not want to go and change it all over the stored procedures we are in the design processes _______________________________________-I know about select '@'+column_name+' ',data_type+coalesce('('+cast(character_maximum_length as varchar(10))+')','') from information_schema.columnswhere table_name='your_table'________________________________________-my stored procedure looks likecreate PROCEDURE [dbo].[usp_UserValidateLogin] ( @LoginName nvarchar(30),--i want instead or writing nvarchar(30) to pull it automatically @OrganizationID int, @Password nvarchar(20),sarah |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-09 : 17:55:12
|
| I don't think this is supported in SQL server at all.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
yaraRaha
Starting Member
8 Posts |
Posted - 2010-04-14 : 15:54:10
|
| sql server dont support calling store procedure in function.why?please help me. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-14 : 18:13:29
|
quote:
Originally posted by yaraRaha
sql server dont support calling store procedure in function.why?please help me.
I believe it's because you're not supposed to make modifications to the database from within a function.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
yaraRaha
Starting Member
8 Posts |
Posted - 2010-04-15 : 17:09:30
|
quote:
Originally posted by DBA in the making
[quote]Originally posted by yaraRaha
sql server dont support calling store procedure in function.why?please help me.
I believe it's because you're not supposed to make modifications to the database from within a function.------------------------------------------------------------------------------------thanks DBA in the making ,but I don't understand what you say ! please say your purpose in detail and I am Sorry because I don't speak English well |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-15 : 17:49:26
|
| Functions aren't supposed to perform updates to the database. Stored procedures are allowed to perform updates, so if you created a function that called a stored procedure, then the function would have the possibility of causing updates to the database, which is not allowed.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
yaraRaha
Starting Member
8 Posts |
Posted - 2010-04-16 : 03:14:30
|
[quote]Originally posted by DBA in the making
Functions aren't supposed to perform updates to the database. Stored procedures are allowed to perform updates, so if you created a function that called a stored procedure, then the function would have the possibility of causing updates to the database, which is not allowed.------------------------------------------------------------------------------------thanks a lot. |
|
|
|
|
|
|
|
|
Stored procedure parameter mapped to table column