Having store procedure instead of View

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Having store procedure instead of View

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2015-03-17 : 12:33:22

I have the following code to create a view. Instead of having a view I would like to convert this code to create a store procedure so the data is saved in a table.

Thanks

SELECT TOP (100) PERCENT StockCode, MAX(categ) AS categ, MAX(flavor) AS flavor, MAX(Type) AS Type
FROM (SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, RTRIM(AlphaValue) AS flavor, RTRIM(AlphaValue) AS Type, DateValue
FROM companyB.dbo.AdmFormData WHERE (0 = 1)
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, NULL AS flavor, NULL AS Type, DateValue
FROM companyB.dbo.AdmFormData AS AdmFormData_3
WHERE (FieldName = 'categ')
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, NULL AS categ, RTRIM(AlphaValue) AS flavor, NULL AS Type, DateValue
FROM companyB.dbo.AdmFormData AS AdmFormData_2
WHERE (FieldName = 'flavor')
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, NULL AS categ, NULL AS flavor, RTRIM(AlphaValue) AS Type, DateValue
FROM companyB.dbo.AdmFormData AS AdmFormData_1
WHERE (FieldName = 'type')) AS a
GROUP BY StockCode, DateValue
ORDER BY StockCode, DateValue

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 12:43:27

insert into table_to_save_data (...your columns...)
select ...your columns...
from yourview

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-17 : 12:47:49

[code]Create Procedure mytableinsert
as

Begin

Insert into mytable -- table you want to insert into, include column names (column1, column2) etc

SELECT TOP (100) PERCENT StockCode, MAX(categ) AS categ, MAX(flavor) AS flavor, MAX(Type) AS Type
FROM (SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, RTRIM(AlphaValue) AS flavor, RTRIM(AlphaValue) AS Type, DateValue
FROM companyB.dbo.AdmFormData WHERE (0 = 1)
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, NULL AS flavor, NULL AS Type, DateValue
FROM companyB.dbo.AdmFormData AS AdmFormData_3
WHERE (FieldName = 'categ')
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, NULL AS categ, RTRIM(AlphaValue) AS flavor, NULL AS Type, DateValue
FROM companyB.dbo.AdmFormData AS AdmFormData_2
WHERE (FieldName = 'flavor')
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, NULL AS categ, NULL AS flavor, RTRIM(AlphaValue) AS Type, DateValue
FROM companyB.dbo.AdmFormData AS AdmFormData_1
WHERE (FieldName = 'type')) AS a
GROUP BY StockCode, DateValue
ORDER BY StockCode, DateValue
End

-- to run
exec mytableinsert
GO
[/code]
We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources